Subquery (IN) is more efficient than JOIN
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 May 2011 23:16:55 +0000
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)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
