| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: EXISTS/NOT EXISTS vs. IN/NOT IN
emreed1234_at_my-deja.com wrote:
>
> I've read there are two advantages of EXISTS. One is that EXISTS can
> perform faster than IN under certain circumstances. The other
> advantage I've heard is that there are some queries that cannot be done
> with IN, but can be done with EXISTS.
>
> Could someone post the reasoning behind performance gains?
>
> Could someone also post a good example of when only EXISTS will work?
>
> Thanks!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
This is somewhat of a generalisation - in the "ol days" NOT IN typically ran like a dog. In later releases, the NOT IN performance has improved (ie used similar access paths to NOT EXISTS).
Similarly with IN and EXISTS - although are often (not always) better expressed as a join (predominantly to yield the newer and generally faster) HASH join plan.
HTH
--
We are born naked, wet and hungry...then things get worse Received on Tue Mar 28 2000 - 05:57:46 CST
 |
 |