At 22:55 18/09/2008, you wrote:

>Hi folks,
>
>
>I just had a brief question.
>
>I remember from Helen Borrie's book that it said that the IN()
>predicator doesn't use indexes to select the results. However, I know
>this is old data.

Nope - at least I hope not. NOT IN() can't use indexes, obviously.

>The question is, does this still apply, and if it does are there any
>plans to change/fix this to use indexes?

Sort of like "Do you have any plans to stop beating your wife?" ;-)

>If it does still apply, what would be the recommended equivalent
>approach in SQL to take instead to maintain a useful level of performance?

Use IN() for a list of constants (within reason) - it resolves to a series of Or'ed equality tests. Try something different (such as an outer join) to do the NOT IN() logic if you possibly can (eliminating matches using WHERE <matched key> is null).

IN (subquery expression) gets resolved to an EXISTS() predicate, which is short logic and, at worst, will walk the whole result. For your NOT IN(), NOT EXISTS() is a near (but not exact) equivalent. Again, the outer join will get a faster result.

./hb