Hi Subbiah, it might be 10x slower, but the estimated costs are all very similar: all around 1800. apparently, the CBO "slightly" overestimates the advantages of the semi-join in your case :-) by the way, I actually think the SEMI JOIN plan looks very elegant too, compared with the 8i plan -- but who cares about elegant execution plans, right? Kind regards, Lex. --------------------------------------------- visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Subbiah, Stalin Sent: Wednesday, June 30, 2004 18:36 To: oracle-l@xxxxxxxxxxxxx Subject: Nested Loop Semi Join Has anyone seen this behavior on sql's with IN subqueries go into nested loop semi join which is 10x times slower than 8174. However after hinting with /*+ ordered no_expand */ I get much better performance than 8i. Query in 8i with explain plan. SQL> select count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152); COUNT(*) ---------- 40441 Elapsed: 00:01:28.59 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12814 Card=1 Bytes=19) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=12814 Card=3852 Bytes=73188) 3 2 VIEW OF 'VW_NSO_1' (Cost=16 Card=125 Bytes=1625) 4 3 SORT (UNIQUE) (Cost=16 Card=125 Bytes=1375) 5 4 INDEX (RANGE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) (Cost=3 Card=126 Bytes=1386) 6 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12788 Card=34385176 Bytes=206311056) Query in 9205 with optimizer_features_enable=920, cpu_costing = off SQL> select count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152); COUNT(*) ---------- 40539 Elapsed: 00:13:59.22 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12791 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (SEMI) (Cost=12791 Card=3883 Bytes=66011) 3 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12789 Card=34385176 Bytes=206311056) 4 2 INDEX (UNIQUE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) Query in 9205 with optimizer_features_enable=920, cpu_costing = off, after hinting SQL> select /*+ ordered no_expand */ count(*) from hffact where page_hit_key in (select page_hit_key from page_hit where account_key=21152); COUNT(*) ---------- 40539 Elapsed: 00:01:10.56 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12816 Card=1 Bytes=17) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=12816 Card=3858 Bytes=65586) 3 2 SORT (UNIQUE) 4 3 INDEX (RANGE SCAN) OF 'PAGE_HIT_WH_U1' (UNIQUE) (Cost=3 Card=125 Bytes=1375) 5 2 TABLE ACCESS (FULL) OF 'HFFACT' (Cost=12789 Card=34385176 Bytes=206311056) Thanks, Stalin ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------