Index distinct keys and performance

[CHALAVADI]

I have a table with 30 million rows.
One of the table's column is date column -- target_date not null. Non-unique Index has been created on this column.
Data was populated into this column using TRUNC(SYSDATE) function.
Daily volume of this table is around 50,000 rows.
USER_INDEXES Distinct_keys column's value is 605.
Index selectivity percentage is .002.
Can someone please advise if the index on this column is good to have or not. Thanks in advance.

[jmodic]

Can someone please advise if the index on this column is good to have or not.

Noone can tell you that, not without knowing some other details. Like what kind of queries are you expecting to benefit from this index, what kind of DML operations are regularly performed on this table, what type of database this is (OLTP, DW) etc....

In any case, if this turned out to be a good candidate for a B-tree index, I'd certanly consider using index compression on it.

[CHALAVADI]

Database is for an heavy OLTP application. Queries are based on target_date column with date range between 1 and 15 days.

[slimdave]

Whether the index is useful or not is going to depend on the query, and the range of dates that the query is selecting is critical. Much depends on the clustering factor of the index, whether you are using bind variables, the statistics on the table & column & index etc..

I think that what you really need is a copy of Jonathan Lewis' book "Cost-based Oracle Fundamentals" to understand how the optimizer will work out whether to use an index or not, because it's a non-trivial topic.