RE: Fav. Urban Legend...

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sat, 16 Mar 2002 10:18:19 -0800
Message-ID: <F001.0042B6D7.20020316101819@fatcity.com>

> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Saturday, March 16, 2002 8:58 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Fav. Urban Legend...
>
> 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)
>

You can see something similar in 8.1.7 with correlated EXISTS if always_semi_join = hash. 8.1.7 (and back through 7.3 I believe) can turn EXISTS correlated sub-queries away from an NL approach into a HASH SEMI JOIN (or merge semi join depending upon the parameter). The plan will not read quite the same as the UNNEST hint approach:

SQL> alter session set always_semi_join = hash;

Session altered.
SQL> select *
  2 from code_master cm
  3 where exists (select null

  4                from code_detail cd
  5                where cm.code = cd.code)

  6 /

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7089 Card=99900 Bytes=1498500)

   1 0 HASH JOIN (SEMI) (Cost=7089 Card=99900 Bytes=1498500)    2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=100000 Bytes=1100000)

   3 1 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=214 Card=299600 Bytes=1198400)

SQL> alter session set always_semi_join = nested_loops;

Session altered.

With the UNNEST hint, I assume you were seeing something similar to the following:

  1 select *
  2 from code_master cm
  3 where exists (select /*+ UNNEST */ null

  4                from code_detail cd
  5*               where cm.code = cd.code)

SQL> / Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4070 Card=99900 Bytes=2397600)

   1 0 MERGE JOIN (SEMI) (Cost=4070 Card=99900 Bytes=2397600)    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=340 Card=100000 Bytes=1100000)

   3 2 INDEX (FULL SCAN) OF 'CODE_MASTER_PK' (UNIQUE) (Cost=188 Card=100000)

   4    1     SORT (UNIQUE) (Cost=3516 Card=299600 Bytes=3894800)
   5    4       VIEW OF 'VW_SQ_1' (Cost=214 Card=299600 Bytes=3894800)
   6    5         INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE)

(Cost=214 Card=299600 Bytes=1198400)

And since you mentioned you hadn't seen the "unnesting" of correlated sub-queries in 8.1.7, I assume you *have* seen it in 9i where the always_semi_join and always_anti_join parameters became undocumented parameters? Things start to get *really* interesting with the way the CBO can transform and choose access paths for NOT IN / NOT EXISTS and IN / EXISTS. For example, a NOT EXISTS can now (9i) be transformed into a HASH or SEMI anti-join. Don't think that was possible in earlier versions (or at least I couldn't make it happen)

This also has a downside in a way. For example, in 8i with always_anti_join set to hash, if I *know* a correlated nested loops anti-join approach is preferred, I can code a correlated NOT EXISTS and rely upon a nested loops anti-join. On the other hand, if I *know* the criteria and data is such that a hash anti-join is preferable for that query, I would code the query using a NOT IN, and assuming the condition for a hash anti join are met, I would get the hash anti join. I can't depend on that in 9i unless I set the "_always_anti_join" parameter. Hopefully the CBO will make the right choices and I will not have to set it or worry about it.

Larry G. Elkins

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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

Received on Sat Mar 16 2002 - 12:18:19 CST