Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is Richard Niemiec Right
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1064119922.65845_at_yasure... Ryan wrote:
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1064114777.554698_at_yasure...
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) when is it faster to use minus? I have found that in is faster if the sub-query is less 'costly' than the outer query by a considerable margin. I dont mean costly by the 'cost' indicator though. Less rows, doesnt have a group by, etc... less work to do. what about minus? I'm not trying to be arrogant here ... but it is faster when it is faster. There are no, well almost no, rule that can be blindly followed: Here are a few SQL statements to chew on. Assume a record ratio of 1:10 between the tables servers and serv_inst.Received on Sun Sep 21 2003 - 11:54:18 CDT
-T1
SELECT srvr_id FROM servers INTERSECT SELECT srvr_id FROM serv_inst; SELECT STATEMENT Cost = 8
-T2
SELECT srvr_id FROM servers WHERE srvr_id IN ( SELECT srvr_id FROM serv_inst); SELECT STATEMENT Cost = 4
-T3
SELECT srvr_id FROM servers WHERE srvr_id IN ( SELECT i.srvr_id FROM serv_inst i, servers s WHERE i.srvr_id = s.srvr_id); SELECT STATEMENT Cost = 5
-T4
SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT STATEMENT Cost = 5
-T5
SELECT DISTINCT srvr_id FROM servers WHERE srvr_id NOT IN ( SELECT srvr_id FROM servers MINUS SELECT srvr_id FROM serv_inst); SELECT STATEMENT Cost = 3
-T6
SELECT srvr_id FROM servers s WHERE EXISTS ( SELECT srvr_id FROM serv_inst i WHERE s.srvr_id = i.srvr_id); SELECT STATEMENT Cost = 4 This is part of the classroom demo for explain plan in my class and each of these six statements returns the exact same result set. And yes I know enough about EXPLAIN PLAN and 'Cost' to understand its strengths and weaknesses so lets not rehash that here and stray from the point. The above was run on 9.2.0.4 with W2K. The results are not the same if performed on the same machine, with the same data, also with a freshly run DBMS_STATS using 8.1.7.4. With 8.1.7.4 the ordering is different. Thus my statement ... that no one can make a blanket statement and expect it to be right all of the time. There are places to start that are more likely to be right than others. But that is all. Just like in quantum mechanics ... it is a question of probabilities. -- 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) Thanks for the examples. Ive used some. Some I have not. I agree you cant make 'broad generalizations', however if tuning 'depends' then it depends on something, so there has to be some narrow generalizations you can make so you have an idea where to start when tuning a query.