one little addendum. if you go to ixora.com.au(great site) you will see
several scripts that use 'ORDERED', however, he is hitting the data
dictionary. Until version 10, the system tablespace is still using the RBO.
"Ryan" <rgaffuri_at_cox.net> wrote in message
news:T73bb.336$0Z5.318_at_lakeread03...
>
> "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 - 16:00:32 CDT
