Re: Subquery (IN) is more efficient than JOIN
- From: Matt McClernon <mccmx@xxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Jun 2011 23:01:03 +0000
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
