Re: Is Richard Niemiec Right

"joe bayer" <joebayerii(no-spam)@hotmail.com> wrote in message news:sb0bb.2357$jo2.1281_at_nwrddc03.gnilink.net...

> 1) we should put most selective condtion in the where clause to eliminate
> the most unqualified rows

I don't think this one was ever true. Even in the RBO-only days. The order of predicates in the "where" clause was not significant. It might have been in a point release somewhere, but that most surely would not have lasted long as it would break just about any code written prior to it. The order of tables in the "from" clause was of course significant.

> 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.

Exactly what is "the table which join with the other two tables"? The entire phrase is non-sense from the semantic point of view, let alone the technical correctness. You sure this is how it was written?

Now, IF you want to write your statements in a defensive fashion so that they will work well with ot without the CBO (in case the user has CBO on but no stats), there MAY be a case for writing them using the old RBO rules.

This of course doesn't make sense with 10G. But with any prior release (and they are still the majority of installs out there) it may serve a purpose to write SQl using the RBO "rules". I guess that's the spirit in which Niemic's book was written. Because if it was for any other reason, it is wrong.

> 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.

with the CBO on. Ie, CHOOSE and statistics gathered.

> 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.

80000 is an overkill in almost all versions of Oracle. But watch what happens in older versions (<9i) if you change this parameter: the CBO behaviour changes significantly. As for the 10 tables, I've never seen that confirmed anywhere. I've seen 5, 10 and 16 as the number of tables in a join where the CBO "gives up". Is there a definitive argument for this one? Dunno.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam