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)?
Asked
Active
Viewed 5,027 times
0
-
1On the contrary, indexes work best when there are many distinct values. However you didn't state what you want to ask. It's like stating that Ferrari is a very good car. (It is ... unless you want to for example move several cows then it's useless) – Michal Pravda Jul 08 '09 at 09:50
2 Answers
4
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.
dpbradley
- 11,645
- 31
- 34
-
In this case, I'm looking for samples created in about the last week, so I just say `WHERE created_on > SYSDATE - 7` – Steven Jul 07 '09 at 20:05
3
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
