Why Oracle Indexes Sometimes Get Suppressed

Building the perfect system with all of the correctly indexed columns does not guarantee successful system performance. With the prevalence in business of bright-eyed ad-hoc query users comes a variety of tuning challenges. One of the most common is the suppression of perfectly good indexes.

A modification of the column side of a WHERE clause often results in that index being suppressed (unless function-based indexes are utilized or the super-smart optimizer figures out a better path). Alternative methods for writing the same query do exist that do not modify the indexed column. A couple of those examples are listed next.

Oracle does use the indexes in many cases, internally fixing the suppression (they continue to get better at this from version to version), especially when an index search or a full index scan can be run instead of a full table scan. If you use 3GL code or code within applications, the results vary, so I continue to show these areas that are a problem with certain tools or applications for you to consider when you run into that full table scan that you didn’t expect. A math function is performed on the column:

 

 

The math function is performed on the other side of the clause (Oracle often fixes this internally):

 

 

A function is performed on the column:

 

 

 

 

 

 

The function is rewritten so the column is not altered (a LIKE or function-based index would fix this):

 

 

 

 

 

 

 

As I stated previously, Oracle is often smart enough to figure out the issue and still use the index. The following query shows that the index is scanned with no table access despite the attempt to suppress the index (adding zero (0) or using an NVL gave the same result). In the following case, everything needed is in the index. Oracle figures out the substring function on the leading edge of the index but is still able to use only the index despite needing both columns from the index (versus using the index to access back to the table).

 

 

 

 

 

 

 

 

 

 

 

 

TIP

At times, modifying the column side of the query can result in the index being suppressed unless a function-based index is used. Oracle may also fix this issue during parsing.