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:09:42 +0000
> 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.
Spot on, I made sure that the contents of the array were unique
My first thoughts were that the IN would stop working once it had gotten its
first match, whereas the JOIN would continue until it had checked all rows in
the driven table. In my mind that would reduce the CPU work done but wouldn't
necessarily reduce the logical read count because the extra rows that the JOIN
would check would all be in buffers that had already been visited.
Matt
--
//www.freelists.org/webpage/oracle-l
Other related posts:
