> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [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).
