Nested Loop Semi Join

From: Subbiah, Stalin <SSubbiah_at_netopia.com>
Date: Wed, 30 Jun 2004 10:36:07 -0700
Message-ID: <F1F84A2E5F9EBD46A9BDB905EB5B10393B08@mxca1.netopia.com>

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Wed Jun 30 2004 - 12:33:24 CDT