Re: Is Richard Niemiec Right

joe bayer wrote:

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

Not true with the single caveat that your statement doesn't look something like this:

SELECT /*+ RULE */ ...
FROM
WHERE We are lucky that Oracle has been around for a long time and is a mature product. But like all good things there is a negative side too. We are steeped in mythology that based on what may have been valid in some murky and distant past but are no longer relevant.

Among my favorite examples of this are:

"When you use a group by clause in your query, all the nongroup expressions in the column clause of the query must appear before the grouped expression in the column clause."

and

"Other types of views that you will learn about in Chapter 7 do not support the use of the order by clause."

Source: OCP Introduction to Oracle 9i: SQL Exam Guide, pages 128 and 163 respectively

Though this, from the same book (page 322), isn't far behind:

In Oracle, indexes can be created on any column in a table except for columns of the LONG datatype.
Apparently the author and reviewers never heard of ORA-02327.

My point here is not to claim superiority over the author and reviewers. Likely they know a lot more than I do. But rather to point out that you don't read something in a book and just implement it. You read it in a book ... and test it extensively.

And if you think EXISTS is always faster than IN or MINUS ... I've got some SQL statements I'd like to sell you. ;-)

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)