Nested Loop Semi Join
- From: "Subbiah, Stalin" <SSubbiah@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 30 Jun 2004 10:36:07 -0700
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
-----------------------------------------------------------------
Other related posts:
