Re: Optimization question: Unrolling subquery into IN clause

From: Wolfram Roesler <wr_at_spam.la>
Date: Wed, 21 Oct 2009 08:18:11 +0000 (UTC)
Message-ID: <Xns9CAB68CF380C7wrgrpde_at_news.albasani.net>

Ind-dba <oraclearora_at_googlemail.com> wrote in news:5e5e5fa9-13b8-4181-b8ad-c6126b861ea3_at_y10g2000prg.googlegroups.com:

> Could you share:
>
> explain plan for
> SELECT several_columns
> FROM tab1
> WHERE col1='Value'
> AND col2 IN
> (
> SELECT /*+ cardinality(20) */ key
> FROM tab2
> WHERE tab2.col3='Something'
> );

---

---

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time
|     | 
-------------------------------------------------------------------------
----- 
|   0 | SELECT STATEMENT   |         |   223K|    24M| 20351   (2)|
|   00:04:05 | 
|*  1 |  HASH JOIN         |         |   223K|    24M| 20351   (2)|
|00:04:05 | *  2 |   TABLE ACCESS FULL| tab2    |  9554 |   149K|   140 

| (2)| 00:00:02 | * 3 | TABLE ACCESS FULL| tab1 | 668K| 63M| |20205 (2)| 00:04:03 |

---

---

Predicate Information (identified by operation id):

---

   1 - access("col2"="key")
   2 - filter("col3"='Something')
   3 - filter("col1"='Value')

> SELECT /*+ gather_plan_statistics */ several_columns
> FROM tab1
> WHERE col1='Value'
> AND col2 IN
> (
> SELECT /*+ cardinality(20) */ key
> FROM tab2
> WHERE tab2.col3='Something'
> );

Exactly identical to the one above, and mostly identical to the version without the hints.

Thanks for your help
W. Rösler Received on Wed Oct 21 2009 - 03:18:11 CDT