This problem normally only arises when the query plan is being
generated by the Cost Based Optimizer. The usual cause is because
the CBO calculates that executing a Full Table Scan would be faster
than accessing the table via the index.
Fundamental things that can be checked are:
. USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the
number of distinct values the column holds.
. USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then
using an index would be preferable to doing a FULL TABLE SCAN. As
the NUM_DISTINCT decreases, the cost of using an index increase thereby is making the index less desirable.
. USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the
rows are in the index. If CLUSTERING_FACTOR approaches the number
of blocks in the table, the rows are ordered. If it approaches the
number of rows in the table, the rows are randomly ordered. In such a
case, it is unlikely that index entries in the same leaf block will point to
rows in the same data blocks.
. Decrease the INIT.ORA parameter
DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost
of a FULL TABLE SCAN cheaper.
. Remember that you MUST supply the leading column of an index, for
the index to be used (unless you use a FAST FULL SCAN or SKIP
SCANNING).
. There are many other factors that affect the cost, but sometimes the
above can help to show why an index is not being used by the CBO. If
from checking the above you still feel that the query should be using
an index, try specifying an index hint. Obtain an explain plan of the
query either using TKPROF with TIMED_STATISTICS, so that one can
see the CPU utilization, or with AUTOTRACE to see the statistics.
Compare this to the explain plan when not using an index.