Re: Subquery (IN) is more efficient than JOIN

>  Your output was hard to read, but the CBO IN operation has a SORT UNIQUE 
> step that the simple RBO join doesn't have.
hmm, it must have got mangled when I pasted it in, does this look any better:

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)

>  This means the inner table was visited in key order for the join, which may 
> have allowed the run-time engine to keep more index blocks >  pinned while 
> accessing the data.
OK, sounds reasonable
>  Run the query two or three times in each version, and check the statistic 
> for "buffer is pinned count". I think you'll find that the drop in "session 
> logical reads" corresponds to an increase in "buffer >  is pinned count".

I ran the 2 queries using Tom Kytes RUNSTATS harness and there was a 
significant difference in 'buffer is pinned count':
STAT...shared hash latch upgra           0      11,049      11,049STAT...buffer 
is pinned count       99,997      73,056     -26,941STAT...consistent gets - 
exami     100,016      73,075     -26,941STAT...consistent gets from ca     
100,679      73,678     -27,001STAT...no work - consistent re     100,672     
 73,671     -27,001STAT...session logical reads       200,720     157,835     
-42,885STAT...consistent gets             200,695     157,802     
-42,893STAT...consistent gets from ca     200,695     157,802     
-42,893LATCH.cache buffers chains         301,507     242,665     
-58,842STAT...sorts (rows)                      0     100,000     
100,000STAT...session pga memory        9,764,864   3,342,336  -6,422,528
                                          --
http://www.freelists.org/webpage/oracle-l

Other related posts: