RE: Correlated subquery performance in 8i 9i
Hello Larry, The better performance of correlated subqueries is accomplished in 9i without manipulating any parameters. It's automatic! As far as I remember, Oracle uses a 'sort-merge' approach and uses the temp file for this. You can reach Gaja @ [EMAIL PROTECTED] Prakash -Original Message- Sent: Saturday, September 07, 2002 11:23 AM To: Multiple recipients of list ORACLE-L 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 4from code_detail cd 5* where cm.code = cd.code) SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=5000 Bytes=65000) 10 FILTER 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=5000 Bytes=65000) 31 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 5 where cm.code = cd.code 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1106 Card=299600 Bytes=5093200) 10 HASH JOIN (Cost=1106 Card=299600 Bytes=5093200) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=340 Card=10 Bytes=130) 31 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 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=831 Card=99900 Bytes=1698300) 10 HASH JOIN (SEMI) (Cost=831 Card=99900 Bytes=1698300) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=10 Bytes=130) 31 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@or91 Connected. SQL set autotrace trace explain SQL select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=770 Card=99900 Bytes=1498500) 10 HASH JOIN (SEMI) (Cost=770 Card=99900 Bytes=1498500) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=77 Card=10 Bytes=110) 31 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 4from code_detail cd 5where 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) 10 NESTED LOOPS (SEMI) (Cost=6 Card=1 Bytes=15) 21 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=4 Card=1 Bytes=11) 3
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 4from code_detail cd 5* where cm.code = cd.code) SQL / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=5000 Bytes=65000) 10 FILTER 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=5000 Bytes=65000) 31 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 5 where cm.code = cd.code 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1106 Card=299600 Bytes=5093200) 10 HASH JOIN (Cost=1106 Card=299600 Bytes=5093200) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=340 Card=10 Bytes=130) 31 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 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=831 Card=99900 Bytes=1698300) 10 HASH JOIN (SEMI) (Cost=831 Card=99900 Bytes=1698300) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=65 Card=10 Bytes=130) 31 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@or91 Connected. SQL set autotrace trace explain SQL select * 2 from code_master cm 3 where exists (select null 4from code_detail cd 5where cm.code = cd.code) 6 / Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=770 Card=99900 Bytes=1498500) 10 HASH JOIN (SEMI) (Cost=770 Card=99900 Bytes=1498500) 21 TABLE ACCESS (FULL) OF 'CODE_MASTER' (Cost=77 Card=10 Bytes=110) 31 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 4from code_detail cd 5where 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) 10 NESTED LOOPS (SEMI) (Cost=6 Card=1 Bytes=15) 21 TABLE ACCESS (BY INDEX ROWID) OF 'CODE_MASTER' (Cost=4 Card=1 Bytes=11) 32 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=3 Card=1) 41 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
Re: Correlated subquery performance in 8i 9i
I also discovered that at Gaja's seminar. Isn't that special? What will Oracle do for us next? Jared BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 12:11 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: 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: BALA,PRAKASH (Non-HP-USA,ex1) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Correlated subquery performance in 8i 9i
so I have to rewrite the inline view query I just managed to get working? sigh --- [EMAIL PROTECTED] wrote: I also discovered that at Gaja's seminar. Isn't that special? What will Oracle do for us next? Jared BALA,PRAKASH (Non-HP-USA,ex1) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/05/2002 12:11 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: 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: BALA,PRAKASH (Non-HP-USA,ex1) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
