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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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:
create index tbl_xdate_idx on tbl(trunc(xdate)).