Why Isn’t My Index Getting Used?-Indexes-1

There are many possible causes of this. In this section, we’ll take a look at some of the most common.

Case 1
We’re using a B*Tree index, and our predicate does not use the leading edge of an index. In this case, we might have a table T with an index on T(x,y). We query SELECT * FROMT WHERE Y = 5. The optimizer will tend not to use the index since our predicate did not involve the column X—it might have to inspect each and every index entry in this case (we’ll discuss an index skip scan shortly where this is not true). It will typically opt for a full table scan of T instead. That does not preclude the index from being used. If the query was SELECT X,Y FROM T WHERE Y = 5, the optimizer would notice that it did not have to go to the table to get either X or Y (they are in the index) and may very well opt for a fast full scan of the index itself, as the index is typically much smaller than the underlying table. Note also that this access path is only available with the CBO.

Another case whereby the index on T(x,y) could be used with the CBO is during an index skip scan. The skip scan works well if—and only if—the leading edge of the index (X in the previous example) has very few distinct values and the optimizer understands that. For example, consider an index on (GENDER, EMPNO) where GENDER has the values M and F, and EMPNO is unique. A query such as
SQL> select * from t where empno = 5;

might consider using that index on T to satisfy the query in a skip scan method, meaning the query will be processed conceptually like this:
SQL> select * from t where GENDER=’M’ and empno = 5
UNION ALLselect * from t where GENDER=’F’ and empno = 5;

It will skip throughout the index, pretending it is two indexes: one for Ms and one for Fs. We can see this in a query plan easily. We’ll set up a table with a bivalued column and index it:
$ sqlplus eoda/foo@PDB1
SQL> create table t asselect decode(mod(rownum,2), 0, ‘M’, ‘F’ ) gender, all_objects.* from all_objects;Table created.
SQL> create index t_idx on t(gender,object_id); Index created.
SQL> exec dbms_stats.gather_table_stats( user, ‘T’ ); PL/SQL procedure successfully completed.

Now, when we query this, we should see the following:
SQL> set autotrace traceonly explain
SQL> select * from t t1 where object_id = 42;Execution Plan

The INDEX SKIP SCAN step tells us that Oracle is going to skip throughout the index, looking for points where GENDER changes values, and read down the tree from there, looking for OBJECT_ID=42 in each virtual index being considered.

Case 2
We’re using a SELECT COUNT() FROM T query (or something similar), and we have a BTree index on table T. However, the optimizer is full scanning the table, rather than counting the (much smaller) index entries. In this case, the index is probably on a set ofcolumns that can contain nulls. Since a totally null index entry would never be made, the count of rows in the index will not be the count of rows in the table. Here, the optimizer is doing the right thing—it would get the wrong answer if it used the index to count rows.

Case 3
For an indexed column, we query using the following and find that the index on
INDEXED_COLUMN is not used:
select * from t where f(indexed_column) = value

This is due to the use of the function on the column. We indexed the values of INDEXED_COLUMN, not the value of F(INDEXED_COLUMN). The ability to use the index is curtailed here. We can index the function if we choose to do it.