Re: Is Richard Niemiec Right

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f6cecca$0$23150$afc38c87_at_news.optusnet.com.au>...
> "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.

The order of predicates made a difference in other db's. Unify did, IIRC.
>
> > 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?

Maybe he has some other book, but that is not how it is written in my copy. It says "In a three-table join, the driving table is the intersection table or the table that has a join condition to each of the other two tables in the join. Try to use the most limiting table as the driving table (or intersection table) so that your result set from the join of the first two tables is small when you join it to the third table." Seems reasonable enough.

Then later: "Using cost-based optimization and NESTED LOOPS joins as the means of joining the first table in the FROM clause is the driving table (all other conditions being equal), but only the ORDERED hint guarantees this."...

He also says several times about indexes and join conditions.

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

Let's jump on Niemec when he's wrong. And say why he is wrong. This inuendo stuff is tiresome. The more I delve into the book, the better I think it is. He gives references (granted, himself sometimes!).

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

jg

--
@home.com is bogus.
http://www.firesigntheatre.com/chat/logs/fstchat_20030515.html