How to index a date column with null values?
- by Heinz Z.
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
Using a bitmap index on the date column
Using an index on date column and an index on a state field which value is 1 when the date is null
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?
