When Indexes Aren't Used in Oracle
In Oracle, the presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used:
- The optimizer decides it would be more efficient not to use the index. As a rough rule of thumb, on evenly distributed data an index will be used if it restricts the number rows returned to 5% or less of the total number of rows. In the case of randomly distributed data, an index will be used if it restricts the number of rows returned to 25% or less of the total number of rows.
- You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'
- You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
- You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
- You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Note. Index-Skip-Scanning in Oracle9i and above allow indexes to be used even when the leading edge is not referenced.
- The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use the /*+ RULE */ hint.