| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Correlated subquery performance in 8i & 9i
How about a little more info? I'm kind of reading between the lines here but
the comments about correlated queries and things changing in 9i make me
wonder if Gaja was getting at the ability to exploit hash/merge joins for
correlated queries.
For example:
1 select *
2 from code_master cm
3 where exists (select null
4 from code_detail cd 5* where cm.code = cd.code)SQL> / Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=5000 Bytes=65000)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=5000
Bytes=65000)
3 1 INDEX (RANGE SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=3 Card=3 Bytes=12)
For each row returned from code_master, a correlated query would be issued against code_detail (using an index). Now, if I put this in an in-line view, I can get a hash-join approach, which is desired in this case since I am getting every row from each table -- I would *not* want a correlated approach in this case, I would prefer a hash approach:
SQL> select *
2 from code_master cm,
3 (select code 4 from code_detail) cd
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1106 Card=299600 Bytes=5093200)
1 0 HASH JOIN (Cost=1106 Card=299600 Bytes=5093200) 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=340 Card=100000 Bytes=1300000)
3 1 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=131 Card=299600 Bytes=1198400)
So, by going to the in-line view, I get better performance, but this doesn't necessarily mean it is best for all queries, just this query.
Now, about 9i and how things change. I wonder if Gaja was referring to "always_semi_join" becoming an undocumented parameter "_always_semi_join" and the CBO deciding whether to un-correlate a correlated query and use a hash approach. Note that this is also available in 8i if you set "always_semi_join = hash", or use the semi join hints (hash_sj / merge_sj), and the conditions for a semi join are met (and if not, the in-line view approach is a good workaround):
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=831 Card=99900 Bytes=1698300)
1 0 HASH JOIN (SEMI) (Cost=831 Card=99900 Bytes=1698300) 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=100000 Bytes=1300000)
3 1 INDEX (FAST FULL SCAN) OF 'CODE_DETAIL_PK' (UNIQUE) (Cost=131 Card=299600 Bytes=1198400)
Note that a correlated approach for the exists is no longer used. A hash approach, similar to that when using an in-line technique, is available. Now in 9i, the CBO will choose to stay correlated, or, to un-correlate and use a hash/merge approach, if you do not set the "_always_semi_join" parameter.
SQL> connect scott/tiger_at_or91
Connected.
SQL> set autotrace trace explain
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=770 Card=99900 Bytes=1498500)
1 0 HASH JOIN (SEMI) (Cost=770 Card=99900 Bytes=1498500) 2 1 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=77 Card=100000 Bytes=1100000)
3 1 INDEX (FAST FULL SCAN) OF 'CD_CODE_IDX' (NON-UNIQUE) (Cost=208 Card=299600 Bytes=1198400)
Note that the CBO chose a hash approach for the above example. Now, if I do something highly selective on code_master (foo_date = trunc(sysdate) - 200), where I *would* want a correlated approach, note that the CBO chooses the correlated approach as opposed to the hash approach above:
1 select *
2 from code_master cm
3 where exists (select null
4 from code_detail cd 5 where cm.code = cd.code)6* and foo_date = trunc(sysdate) - 200 SQL> / Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=15) 1 0 NESTED LOOPS (SEMI) (Cost=6 Card=1 Bytes=15) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=4 Card=1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=3 Card=1)
4 1 INDEX (RANGE SCAN) OF 'CD_CODE_IDX' (NON-UNIQUE) (Cost=2 Card=299600 Bytes=1198400)
Anyway, Gaja, are you out there? I'm interested in what this person was referring to. Reading between the lines of what the person posted, I am guessing that he was referring to the ability to get a hash join, when desired, by re-writing as an in-line view. And that in 9i the CBO can automagically pick to correlate or hash a correlated query (though we still have the ability to un-correlate a correlated query in 8i).
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> BALA,PRAKASH (Non-HP-USA,ex1)
> Sent: Thursday, September 05, 2002 2:12 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Correlated subquery performance in 8i & 9i
>
>
> Learnt the following from Gaja's seminar last week. So just wanted to pass
> this on:
>
> Inline views works better than correlated subqueries in 8i.
>
> But things have changed in 9i. Gaja proved to us by showing a
> tkprof output.
> This is because Oracle has changed their logic while processing a
> correlated
> subquery.
>
> HTH!
>
> Prakash
-- 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 Sep 07 2002 - 10:23:21 CDT
 |
 |