Re: Subquery (IN) is more efficient than JOIN

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Jun 2011 10:09:53 +0100
Message-ID: <084C88D5BC35494AB5FDADCEDFCCC2B2_at_Primary>

Your output was hard to read, but the CBO IN operation has a SORT UNIQUE step that the simple RBO join doesn't have.

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.

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".

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Matt McClernon" <mccmx_at_hotmail.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, June 01, 2011 12:16 AM Subject: Subquery (IN) is more efficient than JOIN

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

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1375 / Virus Database: 1509/3672 - Release Date: 05/31/11

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 01 2011 - 04:09:53 CDT