Oracle DATE Index

Question

Obviously, an index would work well if a column has a few distinct values.

Will creating an index on a column where nearly every entry is unique be effective (such as a created_on (DATE) column)?

Answer 1

Basically, you should index also columns that are "nearly unique" if you need to query the column in question. Without the index, full table scans (checking all rows) are needed. This has been asked before, so for a longer explanation, please see: should nearly unique fields have indexes

Answer 2

Yes, but remember that the date is stored in an internal format so be careful with equality or join conditions in the WHERE clause. For example, if you're looking for all results for July 7, you'll generally want to have something like "WHERE created_on >= to_date('07/07/2009',/MM/DD/YYYY') and created_on < to_date('07/08/2009',/MM/DD/YYYY')" in the condition. Avoid applying functions to the date column in WHERE clauses - this will prevent the use of the index unless you create a function-based index.