Re: Is Richard Niemiec Right

"joe bayer" <joebayerii(no-spam)@hotmail.com> wrote in message news:sb0bb.2357$jo2.1281_at_nwrddc03.gnilink.net...
> In Richard Niemiec's book "Oracle 9i Performance Tuning, Tips &
> Techniques", Chapter 9, advanced SQL tuning, he mentioned about selecting
> the driving table for table joins, he mentioned
> 1) we should put most selective condtion in the where clause to eliminate
> the most unqualified rows
> 2) in the three table join situation, we should put the table which join
> with the other two tables as the driving table, and put the driving table
as
> first table after the from clause.
>
> Is this statement true?
>
> By testing, I found that changing the table position in the from clause or
> changing the position of join conditions at where clause does not affect
> optimizer at all, if you do not put ORDERED, or RULE hint.
>
> My understanding is, optimizer will calculate all the possibility of all
> possible join conditions up to 80,000 or something like that, so it should
> not matter where you put your table, or which join conditions first,
unless
> it has more than 10 table joins.
>
> You imput is highly appreciated.
>
>

in tom kytes newest book he states that that is a myth. It worked in the days of the RBO, but if you have accurate statistics Oracle will do it for you.

its easy to test... the order matters in the RBO, but is irrelevant in the CBO(if you have accurate statistics). Atleast he took the stuff out about cache/hit ratios this time... Received on Sat Sep 20 2003 - 15:54:14 CDT