Re: Fav. Urban Legend...

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).