Re: Subquery (IN) is more efficient than JOIN

> 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

                                               --
http://www.freelists.org/webpage/oracle-l Received on Wed Jun 01 2011 - 18:09:42 CDT