Subquery (IN) is more efficient than JOIN

From: Matt McClernon <mccmx_at_hotmail.com>
Date: Tue, 31 May 2011 23:16:55 +0000
Message-ID: <COL117-W56F66558055BC705939F56B77A0_at_phx.gbl>

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^_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)
 		 	   		  --

http://www.freelists.org/webpage/oracle-l Received on Tue May 31 2011 - 18:16:55 CDT