Index for date column for range query in Oracle?

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?

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:

• Build a function based index, removing the time element: create index tbl_xdate_idx on tbl(trunc(xdate)).

• 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.

• Remember that searches which span long ranges may need to be handled differently. How you do that depends on the peculiarities of your application.

StackExchange Database Administrators Q#47462, answer score: 7