Re: Subquery (IN) is more efficient than JOIN

From: Jonathan Lewis <>
Date: Thu, 2 Jun 2011 08:11:14 +0100
Message-ID: <D13EA934DA8741D0BAB1EC4E09ECD426_at_Primary>

It;'s a valid consideration, and could work in some cases - it's another one that I mentioned in the article.
Technically you would aim to get Oracle to transform from IN to EXISTS and then run the subquery as a filter:
In your case adding the hint /*+ no_unnest */ should be sufficient.

You may find that this increases the CPU, though, because it probably casts the collection for every row in DOMAINS. The effect on buffer visits will depend pretty much on how Oracle drives through DOMAINS. Regards

Jonathan Lewis

] ----- Original Message -----
] From: "Matt McClernon" <>
] To: <>; <>
] Sent: Thursday, June 02, 2011 12:09 AM
] Subject: Re: Subquery (IN) is more efficient than JOIN

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

Received on Thu Jun 02 2011 - 02:11:14 CDT

Original text of this message