The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.
B*TREE Indexes improve the performance of queries that select a small percentage of
rows from a table. As a general guideline, we should create indexes on tables that are
often queried for less than 15% of the table's rows. This value may be
higher in situations where all data can be retrieved from an index, or where the
indexed columns can be used for joining to other tables.
Oracle implicitly creates indexes on the columns of all unique and primary keys that
you define with integrity constraints. These indexes are the most selective and the
most effective in optimizing performance. The selectivity of an index is the percentage
of rows in a table having the same value for the indexed column. An index's
selectivity is good if few rows have the same value.
A table having 100'000 records and one of its indexed column has 90000 distinct values,
then the selectivity of this index is 90'000 / 10'0000 = 0.9.
lf an index on a table of 100'000 records had only 500 distinct values, then the
index's selectivity is 500 / 100'000 = 0.005 and in this case a query which uses the
limitation of such an index will retum 100'000 / 500 = 200 records for each distinct
value. It is evident that a full table scan is more efficient as using
such an index where much more I/O is needed to scan repeatedly the index and the table.