Re: Subquery (IN) is more efficient than JOIN
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Jun 2011 10:22:48 +0100
I should have pointed out that the two queries aren't logically equivalent.
Your collection won't have a uniqueness constraint so it could contain
duplicates, which would be eliminated by the CBO IN version, but not by the RBO
simple join. The "rows" output from the Rowsource Operation report suggest that
the data in the collection happened to be unique in your example.
If you're a member of the IOUG I wrote an article about optimising a two-table
join for the last issue of their journal - the option for using this rewrite was
one of the ones I mentioned.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
----- Original Message -----
From: "Matt McClernon" <mccmx@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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..?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
