Index for date column for range query in Oracle?

Question

We have a lot:

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

We expect to boost performance by tuning index on XDATE column (which have DATE type).

What type of indexes more suitable for DATE type and between condition?

Answer 1

With details provided two column index can be sensible:

CREATE INDEX tbl_index ON tbl(fld,xdate);

But of course everything depends on your data.

• if your query returns >1% of the table full scan the most probably will be faster. So no need to create index at all
• if FLD has bad selectivity then index on (xdate) may achieve similar results
• every index adds overhead to DML operations. Besides changing the data Oracle also has to change all indexes and also log everything in redo logs.

To gather data just run:

select count(*) from TBL;
select count(*) from TBL where XDATE between ? and ?;
select count(*) from TBL where FLD = ?;
select count(*) from TBL where XDATE between ? and ? and FLD = ?;

And compare results. Or edit your question with those results.

Answer 2

Ranges - especially date ranges - can be hard to tune. There is a word of difference between this ...

where xdate between date '2013-07-01' and date '2013-07-31'

... and this ...

where xdate between date '2003-07-01' and date '2013-07-31'

You would want radically different access paths for those two queries. An index will help with the first one, but will probably be disastrous for the second (depending on the volumes of data involved).

The other snag with indexing dates concerns the time element. If xdate contains the time element then potentially you have 86400 different values for each calendar day. In most cases when we're doing a date range search we want all hits for the day regardless of time.

We can't give an absolute recommendation, because you haven't provided enough details but my general advice would be:

1. Build a function based index, removing the time element: create index tbl_xdate_idx on tbl(trunc(xdate)).
2. Investigate whether index compression will give you a space saving. Compressed indexes usually perform better too. But it's probably only worthwhile if the dates don't include times, another benefit of the FBI approach. Oh, and you need Enterprise Edition.
3. Remember that searches which span long ranges may need to be handled differently. How you do that depends on the peculiarities of your application.