Re: IN vs. EXISTS ...... NOT IN vs. NOT EXISTS

On Tue, 10 Feb 1998 21:55:02 GMT, peter.schneider_at_okay.net (Peter Schneider) wrote:

>On Mon, 09 Feb 1998 18:40:45 -0800, Kal Khatib <kal_at_gene.COM> wrote:
>
>>I have statements where i use the above oracle functions.
>>IN and EXISTS seem _not_ to be interchangable (same for negation)
>>Could some explain the differnece in behavior between those functions
>>(beside performace.. IN is slower I think)?.
>>I find it tricky to use one vs. the other. I end up having to
>>experimenting with permutation
>>to get the logic right.
>>thanks
>>Kal.
>
>Hi Kal,
>
>you can transform the IN/NOT IN expression into an equivalent
>EXISTS/NOT EXISTS expression by using a correlated subquery.
>Consider this example:
>
>SELECT col_1
> FROM table_1
> WHERE id_1 IN
> (SELECT id_2
> FROM table_2
> WHERE col_2 < some_value)
>
>would transform to
>
>SELECT t1.col_1
> FROM table_1 t1
> WHERE EXISTS
> (SELECT 1 -- any 'dummy' expression will do here
> FROM table_2 t2
> WHERE t1.id_1 = t2.id_2 -- the correlation expression
> AND t2.col_2 < some_value)
>
>If the result sets retrieved by both the outer and the inner query in
>the first example is small enough (based on the criteria and
>selectivity of available indexes) that you would favour an index
>access over a full table scan, the equivalent second variation usually
>gives better performance. The subquery will stop on the first hit, the
>execution plan will usually be a nested loop, and if you have indexes
>on the columns of your correlation expression, only index reads will
>occur. BTW, if you are operating on large quantities of data, you
>should definitely _not_ use the NOT IN operation, it is a real
>performance killer.
>
>HTH
>Peter

The table sizes really drives the performance gain of the EXISTS/NOT EXISTS. The EXISTS will hit t2 for each t1 in the above query. For most queries this is a good idea, but if t2 is increadibly huge and the subquery requires a FTS then the IN could be much faster. Again, I must plug Guy Harrison's - Oracle High Performance Tuning. Don't tune SQL without it. Received on Wed Feb 11 1998 - 00:00:00 CST