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?
With details provided two column index can be sensible:
But of course everything depends on your data.
To gather data just run:
And compare results. Or edit your question with those results.
Ranges - especially date ranges - can be hard to tune. There is a word of difference between this ...
... and this ...
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
We can't give an absolute recommendation, because you haven't provided enough details but my general advice would be: