Re: Is Richard Niemiec Right

"Ryan" <rgaffuri_at_cox.net> wrote in message news:<hy9bb.374$0Z5.69_at_lakeread03>...
> "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.

Ryan, see Daniel's post with a series of queries and costs. Due to the volumne of systems and developers I work with I do not hang on very many tuning efforts. The results potentially vary with every release. Also note when I said rearrange I was referring to the both FROM and WHERE clause re-ordering of the conditions. I just like ordering my FROM clause in what should be the driving table order so that when the query goes to lunch after an upgrade a simple ORDERED or ORDERED USE_NL hint is usually all we need to fix it.

In theory if you give the CBO three unhinted variations of a query that produces the same result set the plan for solving all three query versions should be the same. I am of the belief that the reality is that this condition does not yet exist.

IMHO -- Mark D Powell -- Received on Sun Sep 21 2003 - 16:35:17 CDT