Re: Subquery (IN) is more efficient than JOIN
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Matt McClernon" <mccmx@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Jun 2011 08:11:14 +0100
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
http://jonathanlewis.wordpress.com
] ----- Original Message -----
] From: "Matt McClernon" <mccmx@xxxxxxxxxxx>
] To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
] 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts: