Subquery (IN) is more efficient than JOIN

In the test case below the subquery (IN) SQL visits 25% less buffers than the 
join query.  The :B1 bind variable is an array of strings.
any ideas why the IN does so much less work than the JOIN for the same row 
count and same plan..?

SQL ID: 52a8u971qm7tqPlan Hash: 3545774334SELECT /*+ RULE */ 
DOM_NAMEFROM DOMAINS, TABLE(CAST(:B1 AS DOMAIN_LIST)) DL WHERE DOM_NAME = 
DL.COLUMN_VALUE^@call     count       cpu    elapsed       disk      query   
 current        rows------- ------  -------- ---------- ---------- ---------- 
----------  ----------Parse        1      0.00       0.00          0          0 
         0           0Execute      1      0.01       0.01          0          0 
         0           0Fetch        1      0.86       0.86          0     200047 
         0      115195------- ------  -------- ---------- ---------- ---------- 
----------  ----------total        3      0.87       0.87          0     200047 
         0      115195
Misses in library cache during parse: 0Optimizer mode: RULEParsing user id: 63 
    (recursive depth: 1)
Rows     Row Source Operation------- 
 --------------------------------------------------- 115195  NESTED LOOPS 
 (cr=200047 pr=0 pw=0 time=6355 us)  99704   COLLECTION ITERATOR PICKLER FETCH 
(cr=0 pr=0 pw=0 time=284 us) 115195   INDEX RANGE SCAN DOM_NAME_IDX (cr=200047 
pr=0 pw=0 time=3191 us)(object id 54309)
********************************************************************************
SQL ID: brdpfk7gs8cm7Plan Hash: 2750862036SELECT DOM_NAMEFROM DOMAINS WHERE 
DOM_NAME IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B1 AS  DOMAIN_LIST)))

call     count       cpu    elapsed       disk      query    current       
 rows------- ------  -------- ---------- ---------- ---------- ---------- 
 ----------Parse        1      0.00       0.00          0          0          0 
          0Execute      1      0.01       0.01          0          0          0 
          0Fetch        1      0.78       0.78          0     157986          0 
     115195------- ------  -------- ---------- ---------- ---------- ---------- 
 ----------total        3      0.80       0.80          0     157986          0 
     115195
Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 
63     (recursive depth: 1)
Rows     Row Source Operation------- 
 --------------------------------------------------- 115195  NESTED LOOPS 
 (cr=157986 pr=0 pw=0 time=4720 us cost=536 size=6141 card=267)  99704   SORT 
UNIQUE (cr=0 pr=0 pw=0 time=417 us)  99704    COLLECTION ITERATOR PICKLER FETCH 
(cr=0 pr=0 pw=0 time=259 us) 115195   INDEX RANGE SCAN DOM_NAME_IDX (cr=157986 
pr=0 pw=0 time=3353 us cost=2 size=21 card=1)(object id 54309)
                                          --
http://www.freelists.org/webpage/oracle-l


Other related posts: