How to index a date column with null values?

Posted by Heinz Z. on Stack Overflow See other posts from Stack Overflow or by Heinz Z.
Published on 2010-06-18T10:09:57Z Indexed on 2010/06/18 10:13 UTC
Read the original article Hit count: 257

Filed under:
|
|
|

How should I index a date column when some rows has null values? We have to select rows between a date range and rows with null dates.

We use Oracle 9.2 and higher.

Options I found

  1. Using a bitmap index on the date column
  2. Using an index on date column and an index on a state field which value is 1 when the date is null
  3. Using an index on date column and an other granted not null column

My thoughts to the options are:

to 1: the table have to many different values to use an bitmap index
to 2: I have to add an field only for this purpose and to change the query when I want to retrieve the null date rows
to 3: locks tricky to add an field to an index which is not really needed

What is the best practice for this case? Thanks in advance

Some infos I have read:

Oracle Date Index
When does Oracle index null column values?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about optimization