Re: Why does NOT IT take so long

Rob Verlander <robv_at_accessone.com> wrote:
>tconder_at_csc.com (Tom Conder) wrote:
>>When writing Oracle SQL, why does the NOT IN statement take so long?
>
>A NOT IN clause will always result in a full table scan and any indexes
>that you may have will be IGNORED.

I agree that Oracle always seems to ignore indexes when evaluating NOT IN clauses. I am not convinced that this is necessary.

Consider the following two queries:

1. SELECT x FROM t1 WHERE x NOT IN (SELECT y FROM t2)
2. SELECT x FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE y = x )

If t2 is indexed by y, query 2 will use this index, whereas query 1 will not. It will make repeated full scans over t2.We all learn, after a bit, never to write query 1.

In fact the two queries do not mean the same. The difference is to do with NULLs. Query 1 is in fact the same as:

3. SELECT x FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE y = x )

   AND NOT EXISTS (SELECT 1 FROM t2 WHERE y IS NULL)

I suspect the reason why query 1 never uses the index is that NULLs are not represented in the index.

My gripe is about the case when there is a NOT NULL constraint on column y. In this case, queries 1 and 2 are equivalent. A particularly common example is when y is a primary key.

Even if there isn't a NOT NULL constraint, re-writing 1 as 3 makes it go faster (try it). The first sub-query can be evaluated using the index, and the second is not correlated. It is evaluated by a single full scan.

My conclusion is that the slowness of NOT IN is not necessary. It is caused by incompetent optimisation.

Would anyone from Oracle care to comment?

Charles Jardine, Univ of Cambridge UK Received on Tue Jul 25 1995 - 00:00:00 CEST