Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is Richard Niemiec Right

Re: Is Richard Niemiec Right

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 20 Sep 2003 17:34:21 -0700
Message-ID: <2687bb95.0309201634.79e14a35@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 -- Received on Sat Sep 20 2003 - 19:34:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US