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?

Edit

Our table has 300,000 records. 1,000 to 10,000 records are inserted and delete every day. 280,000 records have a null delivered_at date. It is a kind of picking buffer.

Our structure (translated to english) is:

create table orders
(
  orderid              VARCHAR2(6) not null,
  customerid           VARCHAR2(6) not null,
  compartment          VARCHAR2(8),
  externalstorage      NUMBER(1) default 0 not null,
  created_at           DATE not null,
  last_update          DATE not null,
  latest_delivery      DATE not null,
  delivered_at         DATE,
  delivery_group       VARCHAR2(9),
  fast_order           NUMBER(1) default 0 not null,
  order_type           NUMBER(1) default 0 not null,
  produkt_group        VARCHAR2(30)
)

"Our table has 300,000 records.... 280,000 records have a null delivered_at date. "

In other words almost the entire table satisfies a query which searches on where DELIVERED_AT is null. An index is completely inappropriate for that search. A full table scan is much the best approach.

If you have an Enterprise Edition license and you have the CPUs to spare, using a parallel query would reduce the elapsed time.

more