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.

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_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..?

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 01 2011 - 04:22:48 CDT