Another comment that Gaja made in his note
was that he didn't like using underscore parameters
such as
_unnest_subquery = true.
especially since you can't be sure of the impact
of using a 'functionality' hint globally. And I totally
agree - particularly in this case where we know that
unnesting can make the performacne worse, and
we can't know whether, in its version 8 form, the
parameter forces unnesting unconditionally even
when the optimizer would otherwise cost against it.
However - the fact that the parameter is there
reminded me that Oracle 9 has a hint UNNEST -
so I thought I'd check if Oracle 8 has got it as
well. It isn't in my 8.1.5 manuals, (anyone care
to check the 8.1.7 for me) but it's there and
it works.
So - when you get to that tricky query which
looks as if it could be unnested, but you can't
quite figure out how, maybe all you need to
do is turn:
select
from
where
..... (select colx
from ....
where ...
)
into
select
from
where
..... (select /*+ unnest */ colx
from ....
where ...
)
and if Oracle can unnest the query, Oracle
will unnest the query; for example, in the
case of the SQL Gaja's used in paper, the
subquery SQL will produce an execution
plan matching the join SQL, with a line
VW_SQ_1
as one of the 'tables' in the hash join.
(Actually Oracle 8.1.7 will do this for
some subquery operations without the
hint - but so far none of the ones I've seen
it in are correlated subqueries)
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 <[EMAIL PROTECTED]>
Date: 16 March 2002 10:37
|
|
|On that line, I've just had a note from Gaja
|about my commentary on the line:
|
| "Rewrite all correlated subqueries using in-line views".
|
--
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).
