Re: Is Richard Niemiec Right

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2687bb95.0309201634.79e14a35_at_posting.google.com...
> "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.
>
> Joe, what version of Oracle did you do your testing on. In the past
> from version 7 and up and including 8.1.7.4 I have observed optimizer
> plan changes from rearranging my queries. As a general rule you
> should code your from clause in (left to right or you could also say
> top to bottom) desired driving table order if for no other reason than
> it is good documentation. Also Oracle support infomed me that Oracle
> has reduced the number of default permutations the optimizer will go
> through in version 9. You might want to check GV$SYSTEM_PARAMETER or
> the underlying x$ tables to see what your system is using.
>
> The specific information your referenced from Mr Niemiec's book
> appears to be reasonable advice in my opinion though the wording for
> item 2 could stand some improvement. There were numerous errors or
> questionable statements in his first editon tuning book, but not
> everything he says is wrong. Unfortunately a lot of advice he gave in
> the first edition appears to have its roots in earlier versions of
> Oracle and is questionable at best for the more current versions of
> Oracle.
>
> I follow the test and observe school, but what you saw and what I have
> observed do not match up. It could have to do with the version and
> sample SQL chosen. It is very hard to create tests that cover the
> full range of possibilities.
>
> IMHO -- Mark D Powell --

you got any test cases on this with metrics? version numbers? systems. Id like to see them. Received on Sat Sep 20 2003 - 23:12:04 CDT