Re: Index on date-fields with NULL values

"Aad Aldus" <a.aldus_at_modality.nl> wrote in message news:<3f030622$0$49107$e4fe514c_at_news.xs4all.nl>...
> Ik have created an index on a date field.
> 99% of all data in the table has a filled in date. 1 % is NULL
> When I do a select with clause ... WHERE ADATE IS NULL,
> Oracle does not use the index.
>
> Is this correct? or How can I force Oracle to use the index.
>
> Kind reagrds,
> Aad.
>
> PS: I am using Oracle 8.1.7 and 9.2

This is correct. If all of the columns of an index are null that record is not included in the index.
NULL means *nothing*.
Hence IS NULL will NEVER use an index.

You cannot force Oracle to use the index (this is documented behavior, and has always worked this way) other than - use a function based index (Oracle EE edition only, and in that case -especially if only 1 percent is null- you are killing an insect with a sledgehammer)
- use a dummy date for the null values

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Jul 03 2003 - 09:49:02 CEST