Diagnosing Why a Query is Not Using an Index

Diagnosing Why a Query is Not Using an Index

PURPOSE

 This article is intended to assist with the question : Why is my index not used? 

TROUBLESHOOTING STEPS

Understanding 'Why my index is not being used?' is a big topic as there are many varied reasons for indexes not being used.
The following is a helpful checklist. Click on the links below to jump to the relevant section of the article:

Quick Initial Checks

• The table is indexed, isn't it?

  Check that the table which you believe should be accessed via an index actually has indexes defined on it. The indexes could have been dropped or could have failed to create - For example, it is possible, after importing tables, or performing loads, that indexes were not created due to errors (either software or human). The following query shows an example of checking for indexes:

   SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;
  

• Why SHOULD the index be used?

  Oracle does not have to use an index simply because an index exists. If a query requires every row in the table to be queried (in table joins, for example), then why query all the rows in the index AND all the rows in the table? Ignoring the index in this case results in better performance. The optimizer makes decisions based on statistics regarding the suitability of various access methods, including indexes, for all queries and chooses the best one.

Issues with the index itself

• Are the indexed columns/leading column of the index supplied in the where clause of the query (predicate list) as a single table (non-join) predicate?

  If not, then remember that at least the leading column of an index is required in the predicate list to use an index in a query (but see Skip Scan below.)
  
  Example: You have defined index EMPNO_I1 on single column EMP.EMPNO, and defined concatenated index EMPNO_DEPT_I2 on columns EMP.EMPNO and EMP.DEPT (EMP.EMPNO is leading column). In order for the optimizer to consider either index, you must use the column EMP.EMPNO in the predicate list (WHERE clause):

   SELECT ename, sal, deptno FROM emp WHERE empno<100 pre="">

  
  Exceptions:
  • CBO can use a Index Fast Full Scan (INDEX_FFS) as long as the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. The leading column of an index is not required for an INDEX_FFS to be performed. Note that the use of an INDEX_FFS does not necessarily return the rows in sorted order. Ordering is dependent on the order that the index blocks are read and rows are only guaranteed to be returned in a sorted order if an 'order by' clause is used. See:
    Document 344135.1 Ordering of Result Data
    Document 70135.1 Index Fast Full Scan Usage To Avoid Full Table Scans
     
  • CBO can use an Index Skip Scan (INDEX_SS). The leading column of an index is not required for an INDEX_SS to be performed. See:
    Document 212391.1 Index Skip Scan Feature
     
  • CBO can choose to use an index to avoid sorting. The indexed columns would need to be in the order by clause for this to happen.
    See
    Document 67409.1 When will an ORDER BY use an Index to Avoid Sorting?
    Document 10577.1 Driving ORDER BY using an Index

• Are the indexed columns part of join predicates?

  For example, the following join predicate defines the join between the emp and dept tables on the deptno column:

   emp.deptno = dept.deptno

  
  If the indexed columns ARE part of join predicates, then: What type of join method is used to implement the join when the query executes?
  
  
  • Hash / Sort Merge Join: With Hash joins and Sort Merge joins, information from the outer table is not available at join time to enable row look ups on the inner table; rather both tables are accessed separately and then the resultant data is joined. The inner table of a Hash or Sort Merge cannot be probed solely using an index based on the join columns . This is an inherent limitation of the implementation mechanisms used by these join types. Nested Loops joins are different in as much as they allow index lookups on the join columns.
    
  • Nested Loops Join: Nested loop joins work by reading the outer table and then using the information gathered to probe the inner table. This algorithm allows index lookups to occur on the inner table.
  
  Only a Nested loops join can allow index lookups on the inner table that are based solely on the join column(s):
  
  Additionally, does the join order allow index usage?
  When using a nested loops join, the outer table of a nested loops join must have been visited BEFORE an index can be used on the inner table. Check the explain plan for the query to determine which access path has been used and what order the tables are being visited. Due to this limitation, the join order of the tables is important.
  
  For example: If we are joining EMP to DEPT using the "emp.deptno = dept.deptno" predicate as above and there is an index on EMP.DEPTNO (assuming there are no other predicates that relate to EMP.DEPTNO in the query), if EMP is visited before DEPT, then no row values are present at that time that can be used to lookup rows in the EMP.DEPTNO index because we have not yet accessed any objects to provide such a lookup key. With this join order, the only way the index could be used is with a full index scan or a index fast full scan (reading the whole index). In this case it is possible that a Full Table Scan (FTS) will cost less and be chosen instead. 

• Are the indexed columns part of an IN list or multiple OR's?

  For example:
  

  emp.deptno IN (10,23,34,....)

  or

     emp.deptno = 10
  OR emp.deptno = 23
  OR emp.deptno = 34
  ....

  
  In this case it is possible that the query has been transformed in to something that cannot use an index. See:
  Document 62153.1 Optimization of large inlists/multiple OR`s
   

• Are the indexed columns modified by functions?

  Indexes cannot be used on columns modified by functions. Function based indexes which get around this limitation. See:
  
  
  Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
  Database Concepts
  Chapter 3 Indexes and Index-Organized Tables
  Overview of Function-Based Indexes
  http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CBBGIIFB
  
  Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
  Database Performance Tuning Guide
  Chapter 2 Designing and Developing for Performance
  Section 2.5.3 Table and Index Design
  http://docs.oracle.com/database/121/TGDBA/pfgrf_design.htm#CJHCJIDB
   

• Is implicit type conversion going on?

  If the datatypes of two values being compared are different, then Oracle has to implement type conversion on one of the values to enable comparisons to be made. This is called implicit type conversion. Typically this causes problems when developers store numbers in character columns. At runtime oracle is forced to convert one of the values and (due to fixed rules) places a to_number around the indexed character column. Adding any function to an indexed column prevents use of the index. The fact that Oracle has to do this type conversion is an indication of a design problem with the application. Because conversion is performed on EVERY ROW RETRIEVED, this will also result in a performance hit. See:
  
  Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
   

• Is it semantically impossible to use an index?

  Because of cost considerations on the query as a whole, a plan may have been chosen that means that the use of an index at a lower level is now not possible. The index may have been considered in other join orders/methods but the method with the lowest cost makes the index unusable.  Because of the way the query has been executed (i.e. join orders/methods) it is now 'semantically impossible' to use an index.

• Is the 'wrong type' of index scan made?

  For example, is an Index fast full scan used as opposed to index range scan? It is possible that the optimizer has chosen the desired index but a different scan method would be preferable to the user. In this case utilise the INDEX_FFS, INDEX_ASC and INDEX_DESC hints to force the scan type that you require. See:
  Document 62339.1 Init.ora Parameter "FAST_FULL_SCAN_ENABLED" Reference Note
  
  Indexes can be defined with ascending or descending sort order. Oracle treats descending indexes as if it were function-based indexes and therefore a different execution plan might be used compared to that used for a default ascending sort order. By examine the execution plan you do not see whether the default ascending order or the descending sort order will be used therefore additionally check the 'DESCEND' column of view DBA_IND_COLUMNS.

• Are the indexed columns NULLable?

  Indexes do NOT store NULL values unless the index is concatenated (i.e. multi-column indexes), or it is a Bitmap index. For concatenated indexes NULLs are only stored if at least one of the indexed columns is filled. Trailing NULLs in concatenated indexes are stored. Rows are not stored if all the indexed columns are NULL. Operations that need to return the NULL values (such as count) may be prevented from using the index because of the lack of NULL values in the index. This is because the optimizer cannot guarantee that it can retrieve the necessary information using the index alone. There are also considerations with using NOT IN predicates and NULL values. See:
  Document 28934.1 Use of indexes with NOT IN subquery
  
  Bitmap indexes are allowed to store NULLs. Therefore, they are considered NULLable and the optimizer may use them whether they are NULL safe or not. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT. Example:

  SELECT count(*) FROM emp;

  
  For more information on Bitmap indexes, see:
  Document 70067.1 All about Bitmap Indexes 

• Is NLS_SORT set to BINARY?

  If NLS_SORT is not set to BINARY, indexes will not be used. This is because indexes are built according to a binary order of keys (pre-sorted using binary values). Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. For more detail on NLS_SORT and index use please reference:
  Document 30779.1 Init.ora Parameter "NLS_SORT" Reference
  Document 227335.1 Linguistic Sorting - Frequently Asked Questions (section 4.)

• Are Invisible Indexes present?

  Beginning with Oracle Database 11g Release 1, you can create invisible indexes or make an existing index invisible. An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Invisible indexes are still maintained during DML statements. See:
  
  
  Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
  Database Administrator's Guide
  Understand When to Use Unusable or Invisible Indexes
  http://docs.oracle.com/database/121/ADMIN/indexes.htm#CIHJIDJG
   

Optimization and Costing Issues

• Are Accurate and Appropriate statistics in place?

  The CBO relies on accurate, up to date and complete statistics to enable it to determine the optimal access plan for a particular query. Ensure that statistics have been gathered if the intention is to use the CBO. Using CBO with no statistics will force the CBO to use predefined defaults which are unlikely to produce a good plan or promote index usage with your application. See:
  Document 754931.1 Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
   

• Remember that the CBO may choose a different index because the costs indicate that this is appropriate. In addition to basic table and index statistics, column statistics should be gathered for columns with a non-uniform data distribution. For advice on gathering statistics see:
  
  Document 1369591.1 Optimizer Statistics - Central Point
  Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
  
  In general, new statistics should be gathered after a schema object's data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. It is also recommended to gather new statistics information after having installed a new patchset. The table access works best when the statistics have been generated by the same version as currently executing. More information about "Why are my queries slow since upgrading the database" can be found in:
  Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

• Does the index have the same rank or cost as another index?

  With equally costed indexes, the CBO uses various ways to break the tie e.g. alphabetical order of index name, bigger NDK (Number of Distinct Keys) for fully matched indexes (not for fast full scans) or index with lower number of leaf blocks. Note that this is unlikely to be a common occurrence. See:
  Document 73167.1 Handling of equally ranked (RBO) or costed (CBO) indexes

• Is the index unselective?

  • The index is unselective
    It may not be a good idea to use it anyway...
  The column data does not have a uniform distribution
  • The CBO assumes that column data is not skewed and is uniformly distributed. If this is not the case then the statistics may not reflect the actuality and indexes may not be chosen for some selective values because of the unselective nature of the column as a whole. If this is the case then consideration should be given to the creation of histograms to record a more accurate picture of column data distribution or alternatively use hints.
  • The optimizer statistics are inadequate making indexes appear unselective
  Possible solutions/workarounds:
  • Gather more accurate stats.See:
    Document 1369591.1 Optimizer Statistics - Central Point
    Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
      
  • Consider gathering column statistics where column data is not uniform
  • Use hints/outlines etc. See
    Document 29236.1 QREF: SQL Statement HINTS
    Document 50607.1 How to specify an INDEX Hint
     

• Table lookup cost is a high proportion of the overall cost

  Typically when an index is used that requires a table lookup to retrieve associated data that is not stored in the index, the cost of that table lookup far exceeds the cost of that actual index lookup. Because the optimizer is costing for the best cost overall, if the table lookup is extremely expensive then that with be highly detrimental to the relative cost of the index as a whole and may, once a certain threshold is reached, cause the optimizer to choose some other access path.
  
  For example

  SELECT empno FROM emp WHERE empno=5

  might use an index on the empno column since all the data required is stored in the index and no table lookup is needed. Whereas, in the following example:

   SELECT ename FROM emp WHERE empno=5

  requires a table lookup because the ename column is not stored in the index. Looking up every ename may become expensive as the number of rows in the query increases.
  
  The optimizer uses a statistic called "Clustering Factor" to determine how much of a table is likely to need to be read following an index scan. See:
  
  Document 39836.1 Clustering Factor
   

• Empty Indexes do not necessarily cost less to scan than full ones

  Reorganization, Truncation or Deletion of data may or may not have cost implications for queries. Remember that deletes do not necessarily free up allocated space from objects. In addition, deletes do not reset the highwatermark for a table. Truncate does. Empty blocks may make indexes/tables appear more expensive than they potentially could be. Dropping and recreating the object will reorganise the structure and may potentially help (or may hinder). This problem is usually most noticeable when comparing the query performance of two different systems with the same data.

• Parameter Settings

  The setting of certain parameters may affect the costing of various objects causing indexes to be favoured or otherwise. For example parameters such as DB_FILE_MULTIBLOCK_READ_COUNT and OPTIMIZER_INDEX_COST_ADJ are recommended to be left at default values in the majority of cases. Unless specific recommendations exist for the type of system in operation, using other values can significantly degrade the performance of some queries if the perceived index cost is reduced or increased to an unrealistic level.

Other Issues

• Are views/subqueries involved?

  Queries involving these structures are likely to be rewritten which may result in indexes not being used (even though one of the goals of the rewrite is to open up additional access paths). This rewrite is known as merging. See:
  Document 199070.1 Optimizing statements that contain views or subqueries

• Are any of the tables remote?

  Often indexes are not used against remote tables. Index usage in distributed queries is dependent on the query that is sent to the remote site. The CBO costs the remote access and will evaluates and compare the costs with and without indexed predicates sent to the remote site. Thus the CBO should make a more informed decision about index usage on remote tables. Building a view on the remote site containing relevant predicates to force index usage and then referencing that in your local query can often help. See:
  Document 68809.1 Distributed Queries

• Is Parallel Execution (PX) involved?

  The index access paths available under Parallel Execution are more restricted than under serial execution. A quick test is to disable parallelism for the query and see if this enables the index to be used.

• Is the query an update with a subquery?

  There may be cases, due to cost considerations why an index is not chosen because it depends on values returned from a subquery. It may be possible to force the index to be used by implementing hints. See:
  
  Document 68084.1 Using hints to optimize an Update with a subquery that is not using an index on the updated table. 

• Does the query use bind variables?

  The CBO cannot generate accurate cost figures for like or range predicates against bind variables. This may result in indexes not being chosen. See:
  Document 68992.1 Predicate Selectivity
   

• Does the query reference a column with a deferrable constraint?

  If a column in a table contains a deferrable constraint column (for example a NOT NULL constraint) and this column is indexed, then we will not consider using the index regardless of whether the constraint currently deferred or explicitly set to immediate. For example:
  
  CREATE TABLE tdc
  ( x INT CONSTRAINT x_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED RELY,
    y INT CONSTRAINT y_not_null NOT NULL,
    z VARCHAR2(30)
  );
  CREATE INDEX t_idx ON tdc(x);
  SET CONSTRAINTS ALL IMMEDIATE; <-- all="" br="" constraint="" deferred="" immediate="" sets="" the="" this="" to="">
  SET AUTOTRACE TRACEONLY EXPLAIN
  SELECT COUNT(1) FROM tdc;        <-- br="" index.="" not="" the="" use="" will="">
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 2532426293
  
  -------------------------------------------------------------------
  | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
  -------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
  |   1 |  SORT AGGREGATE    |      |     1 |            |          |
  |   2 |   TABLE ACCESS FULL| TDC  |     1 |     2   (0)| 00:00:01 |
  -------------------------------------------------------------------
  This is outlined in the following bug, closed as "not a bug":
  
  Bug 17895663  optimizer ignoring deferrable constraints even when not deferred and not in tx
   

• Index hints don't work

  Remember to use table aliases. See:
  Document 69992.1 Why is my hint ignored?
  Document 50607.1 How to specify an INDEX Hint
   

  Useful hints:

  FIRST_ROWS	Likely to promote the use of indexes
  ORDERED	Forces the join order of a query based on the order of tables in the from clause.Oracle recommends using the LEADING hint instead since it has more versatility
  LEADING	The LEADING hint instructs the optimizer to use the specified set of tables to be joined first. This hint is more versatile than the ORDERED hint.
  INDEX	Forces an Index scan. Disables use of FAST mode (INDEX_FFS)
  INDEX_FFS	Forces an Index to be scanned in FAST mode
  INDEX_ASC	Forces an Ascending Index Range Scan
  INDEX_DESC	Forces a Descending Index Range Scan

  
  Also See
  Document 29236.1 QREF: SQL Statement HINTS
   

REFERENCES

NOTE:227335.1 - Linguistic Sorting - Frequently Asked Questions
NOTE:212391.1 - Index Skip Scan Feature
NOTE:344135.1 - Ordering of Result Data
NOTE:70135.1 - Index Fast Full Scan Usage To Avoid Full Table Scans
NOTE:68084.1 - Using hints to optimize an Update with subquery not using index on updated table
NOTE:68992.1 - Predicate Selectivity
NOTE:69992.1 - Why is my Hint Ignored?
NOTE:28426.1 - Partition Views and the use of Indexes (7.1 & 7.2)
NOTE:43194.1 - Partition Views in 7.3: Examples and Tests
NOTE:30779.1 - Init.ora Parameter "NLS_SORT" Reference Note
NOTE:67409.1 - When will an ORDER BY use an Index to Avoid Sorting?
NOTE:10577.1 - Driving ORDER BY using an Index (Oracle7)
NOTE:62153.1 - Optimization of Large Inlists / Multiple OR Conditions
NOTE:754931.1 - Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
NOTE:1369591.1 - Master Note: Optimizer Statistics
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
NOTE:160089.1 - Troubleshooting a Server Upgrade Resulting in Slow Query Performance
NOTE:73167.1 - Handling of equally ranked (RBO) or costed (CBO) indexes
NOTE:50607.1 - How to specify an INDEX Hint
NOTE:29236.1 - QREF: SQL Statement HINTS
NOTE:28934.1 - Use of Indexes with NOT IN Subquery
NOTE:70067.1 - All about Bitmap Indexes
NOTE:199070.1 - Optimizing statements that contain views or subqueries
NOTE:68809.1 - Distributed Queries

====================================================================

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.