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^_at_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 Received on Wed Jun 01 2011 - 18:01:03 CDT