What type of index in best for DATE type on Oracle?

Question

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,

Answer 1

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.

Answer 2

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.

Answer 3

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