Re: IN vs NOT IN

<susana73_at_hotmail.com> wrote in message news:90ohic$n8h$1_at_nnrp1.deja.com...
> Hi there,
>
> I have 2 queries which have same logical meaning. However, the one with
> 'NOT IN' takes forever and the one with 'IN' takes just couple seconds.
> I did a EXPLAIN on them and it looks like the one with 'NOT IN' is doing
> a full table scan for both table that's why it takes so long. Could
> anyone explain to me why?
>
> There are about 100k objectid.

NOT IN or != (not equal to) will not use an index because Oracle doesn't create indexes for what something isn't - it creates indexes for what something is.

Think about it - if you opened a file drawer on a desk and was told to find the file folder marked "Stuff", you probably would have it alphabetized and you'd jump right near the back and find "Stuff" pretty quick. If you were told to find everything except "Stuff", you'd need to grab each folder and evaluate if it's "Stuff" or not.

Well, OK - bad example because what we'd really do as thinking people is find "Stuff" and hold it with one hand and then with the other we'd grab everything else, but Oracle (and probably most other RDBMSs?) just doesn't quite "think" that way.

-Matt Received on Thu Dec 07 2000 - 19:19:00 CET