One of the most common question, when it comes to performance tuning is “Why oracle is not using my index?”. This question, together with her twin question, “Why Oracle is choosing full table scan even when the table is indexed?” cover large part of the performance tuning challenges.

Even well experienced developers and DBA’s can sometimes miss a very obvious reason causing the optimizer not to choose the expected plan. After finding myself time and time again looking for explanations for poor query performance i decided to create a small checklist that i can use to methodically check until I find the issue that is causing the optimizer not to use the index.

This is far from a complete list and the post is probably going to be constantly updated as I encounter ( ok , be reminded of) other performance killing reasons.

1) Are indexed column being changed ?
For example, the index will be used only if the value of the index is not changed in the where clause. Therefore, every manipulation on the “left side” of the where clause will prevent the index from being used. In order to check this you can add the /*+ INDEX(a, i_empno) */ hint if you can not see it being used in the explain plan then there is something in the query that prevent it from being used.

2) Are you using NOT?
The optimizer will not use the index if the query is based on the indexed columns and it contains NOT EQUAL and NOT IN.
3) Are you using LIKE (‘%% ‘) ?
if you use wildcard query with “%” in front of the string, the column index is not being used and a full table scan is required since the % can be replaces by any string. Therefore the optimizer needs to search the contents of every row of that field.
4) Are you using IS (NOT) NULL ?
– Null values are not included in the index. However, this could be worked around by using nvl when creating the index (function based index), adding the PK to the index or even adding a constant to the column: create index emp_dob_idx on emp (date_of_birth,1)
5) Are you using the leading columns in a concatenated index?
remember to put the column with the highest unique values first to make the result set smaller.
6) Are you selecting from a view?
Make sure that that the base tables have appropriate indexes
7) Are the statistics relevant and valid?
8) Does the index exists as all ?
you will be surprised how many times this is the problem
9) Is the query expected to return large portion of the table?
In this case the optimizer will prefer a full scan
10) If you are using subquery you must use only IN or = (you can workaround this by changing it to a join or a function )
11) Did you do a lot of changes on the table recently?
A large number of dml operations on the table might cause the statistics to become stale and stop using the index
12) It is possible that the data on the table is skewed?
The optimizer might expect normal distribution of values between the maximum and minimum values and might choose a wrong plan if this is not the case. For example, using 01/01/0001 or 31/12/4000 as a null values might confuse the optimizer
13) Does your query actually trying to use the index?
is the where clause contains the indexed column?
14) High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans. select DEGREE from dba_tables where table_name='table-name' and owner='owner-name';
15) A full scan will be cheaper than using an index if the table is small.
16) Does it use Other indexes?
You may have other indexes that Oracle perceives as being “better” for the query.

17) Are you implicitly casting types?
Oracle sometimes cast implicitly. For example it might cast varchar2 to number when if the actual values allow (also for dates)
While it might work for a query, it will not use an index when you are joining a table on fields with different types.

18) Wrong Parameters:
optimizer_index_cost_adj – low value reduce the price of indexes use
all_rows access method – The first_rows optimizer mode is more likely to use an index than the all_rows mode.

Bonus advice:
Add the /*+ INDEX(a, col-name) */ hint. if you still can not see the index being used in the explain plan then there is something in the query that prevent it from being used.

Add the /*+ RULE */ hint. If the query uses the index with a RULE hint, you know that the problem is related to the cost-based optimizer (CBO)