469,082 Members | 1,133 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,082 developers. It's quick & easy.

Index on date-fields with NULL values

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
Jul 19 '05 #1
2 22049
"Aad Aldus" <a.*****@modality.nl> wrote in message news:<3f***********************@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
Jul 19 '05 #2
Aad Aldus wrote:
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

Given that 99% of the table-data is returned by the query a Full table
scan is actually faster than using an index.


Michael Willer
Oracle/J2EE architect
Cyber Com Consulting a/s

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by vnl | last post: by
8 posts views Thread by David McDivitt | last post: by
4 posts views Thread by ianv2 | last post: by
5 posts views Thread by Pedro Alves | last post: by
3 posts views Thread by Patrick Hatcher | last post: by
1 post views Thread by William Bradley | last post: by
2 posts views Thread by Thomas F.O'Connell | last post: by
5 posts views Thread by Miquel van Smoorenburg | last post: by
6 posts views Thread by db2admin | last post: by
1 post views Thread by Steffen Stellwag | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.