Optimizer and joins

[Former Member]

Hi,

As mentioned on the wiki, there are different strategies for single table optimization and join optimization and they are listed separately under two different headings.

Does it mean that internally there are two different optimizers viz. one table optimizer and join optimizer? Or is it that these two optimizers are pipe lined? Or is it that there is a single optimizer and the demarcation is just to differentiate between the search strategies?

Moreover, is it true that MAXDB supports only left-deep plans for joins i.e. the base relation is always considered to be the inner operand in the join.?

[lbreddemann · Accepted solution]

> Does it mean that internally there are two different optimizers viz. one table optimizer and join optimizer? Or is it that these two optimizers are pipe lined? Or is it that there is a single optimizer and the demarcation is just to differentiate between the search strategies?

Where is the difference between question 1 and 2?

There are two code paths for single table optimization and join optimization.

To optimize joins you need to know which access pattern are available for each relation - thus you'd run the single table optimizer first and feed the results into the join optimizer.

> Moreover, is it true that MAXDB supports only left-deep plans for joins i.e. the base relation is always considered to be the inner operand in the join.?

No, not true. In BW queries there are multiple hash joined tables before and after fact table access.

That would count as a bushy tree to me.

BTW:

If you're done writing your book on the MaxDB optimizer, don't forget to mention me

regards,

Lars