What type of index in best for DATE type on Oracle?
Solution 1
A regular b-tree index would be appropriate, but if this is a log table with increasing values on the date then look out for index block contention. If you have a lot of sessions inserting new values into the index and those values belong in the same block then you could hit a performance problem. One mitigation for this is a reverse key index, but that makes queries of the type you give more expensive because reverse key indexes cannot support range scans. You would get a full index scan or a fast full index scan instead.
It would also make the index larger because the index block splits would be 50/50, instead of the 90/10 that Oracle uses when it detects a rightward growth pattern in the indexed values.
Solution 2
A regular index should do just fine. Since it is a log the new entries should always have an increasing date value, never dates in the past, which makes for easy index appending. Not a big slowdown for the inserts.
Only consider more complex indices if you experience problems with the above.
Regards K
Solution 3
I would reconsider partitioning, depending on the volume of data - Oracle can use partition pruning when running the query - which carries the benefit of also being able to easily archive old log data later on.
Comments
-
user3199601 almost 2 years
Basing on your experience with Oracle, what will be the best type and settings for index that you would set on a column of DATE type?
- I don't necessarily need to go for partitioned index.
- It is a logging kind of table.
- You don't really care about unique id as a primary key (in fact date is close enough to be uniques most of the time, but due to nature of it, never will be).
Will it be fair to create a cluster index?
What I'm interested in is to optimize execution of queries like SELECT * FROM Log WHERE [Date] > '20-06-2009' ORDER BY [Date] DESC, not slowing down inserts massively. (btw. in real world I would use the correct TO_DATE syntax to avoid truncation and missing the index)
Cheers,