Re: cost-based optimizer and column order to determine inner or outer table?

Both optimizers try to the determine the driving table, ie the table with the most selective criteria.
If that doesn't work out, the rule based optimizer reads from right to lef, sequentially.
The cost based optimizer from left to right. So you can 'influence' the optimizer by having the tables in the correct order in the from clause.
In the cost based optimizer this behavior can be forced by using the /*+ORDERED */ hint.

Hth,

Sybrand Bakker, Oracle DBA

PS You'll just have consumed your quota for today :)

David Spaisman <davedba_at_intercall.net> wrote in message news:38DECA4E.86ACFE2C_at_intercall.net...
> Hello:
>
> I would like to get a better understanding of how the the cost-based
> optimizer determines the inner table and the outer table in Oracle 8 or
> Oracle 8i. I have seen some conflicting explanations that the coice is
> based upon the left or right-most listed table in the from clause. The
> rule based optimzerchooses riht to left or is that the cost-based
> optimzer?
>
> Can any one provide an explanation of how the optimizer chooses the left
> or right most table in the from clause as the inner table or outer
> table?
>
> Any information you can provide will be greatly apreciated. Thanks.
>
> David Spaisman
>
Received on Mon Mar 27 2000 - 00:06:14 CST