Interesting stuff, but an interesting example of
how myths take off. Did you notice the directive:
"Rewrite all correlated subqueries using in-line views".
But you can't guarantee that a rewrite can be
achieved that will produce the same results.
If you don't believe that, look at the Niemiec
book, page 48, where one of his performance
successes is to turn a join into a correlated
subquery --which in general WILL NOT RETURN
THE SAME RESULT !!! If you doubt that,
think about the case for the 'real-life' example
where an order has two lines for the same value.
So, what's the bet that two years from now,
you'll be sitting in front of someone saying:
Yes, I KNOW that Gaja says ... but your
rewrite was wrong, and in this case a
rewrite is not possible.
BTW - Oracle 9 will do the transform internally
anyway into the 'corrected' solution, and Oracle 8.1.7
will do the same if you set
"_unnest_subquery=true".
Of course, another argument for being careful
how you state such advice (perhaps "Look for
opportunities to .... because they can ...." is
better than a firm directive) is that there are
always other solutions, such as:
select
dept_no,
emp_no,
sal
from (
select
dept_no,
emp_no,
sal,
avg(sal) over(partition by dept_no) as avg_sal
from emp
) emp_avg
where
sal > avg_sal
;
With a test case I set up, the CPU usage was
slightly higher, but the logical I/O and latching
significantly lower using this approach when
compared to the hash-join method.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
----- Original Message -----
To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]>
Sent: Wednesday, March 13, 2002 11:28 AM
Hey Guys,
Did anyone have a look at Gaja's new paper on myths and folklore
about
Oracle at Craig Shallahamer's site? Wonderful reading.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
