Re: Indexing null dates

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> Yes, nifty. CREATE INDEX docs updated with:
>
> + <literal>NULL</> values are not indexed by default. The best way
> + to index <literal>NULL</> values is to create a partial index using
> + an <literal>IS NULL</> comparison. <literal>IS NULL</> is more
> + of a function call than a value comparison, and this is why a partial
> + index works.

Uh, this is wrong.

NULLs are indexed. It's just that IS NULL cannot take advantage of it due to
technical details. These are NOT the same thing.

Saying "NULLs are not indexed" will confuse people because it will make them
think that they're not present in the index at all which is what Oracle does.

That has real consequences on queries. The most obvious being that

SELECT * FROM foo ORDER BY bar

cannot take advantage of an index on bar. Oracle programmers are accustomed to
having to had a "WHERE bar IS NOT NULL" or else live with the full table scan
and sort.

--
greg