首页 > 数据库 > Diagnosing Why a Query is Not Using an Index

Diagnosing Why a Query is Not Using an Index

2007年6月3日 2,194 views 发表评论 阅读评论
Diagnosing Why a Query is Not Using an Index
Doc ID: Note:67522.1 Type: TROUBLESHOOTING
Last Revision Date: 14-MAR-2007 Status: PUBLISHED

Purpose

This article is intended to assist with the questions:

Why is my index not used?

Scope and Distribution

Support Analysts and customers who are experiencing problems with their query plans under the Rule Based (RBO) or the Cost Based Optimizer (CBO)..

Checklist

Understanding ‘Why my index is not being used?’ is a really big topic. 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? 🙂 Why SHOULD the index be used?

Issues with the index itself

Are the indexed columns/leading column of the index supplied in the where clause of the query? Are the indexed columns part of join predicates?
Are the indexed columns NULLable? Are the indexed columns part of an IN list or multiple OR’s?
Is implicit type conversion going on? Are the indexed columns modified by functions?
Is the ‘wrong type’ of index scan made?

Optimization and Costing Issues

You were using RBO & are now using CBO? Index hints don’t work
Is the index unselective? Does the index have the same rank or cost as another index?
Does the query use bind variables? Is it semantically impossible to use an index?
Deletes do not necessarily free up allocated index space

Other Issues

Is parallel query involved? Are any of the tables remote?
Are views/subqueries involved? Is the query an update with a subquery?
Are partition views involved? Is NLS_SORT set to BINARY?

Details

  • The table is indexed isn’t it? 🙂
  • Why SHOULD the index be used?
  • 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?

    No:
    At least the leading column of an index is required in the predicate list to use an index in a query.

    Example:

      • Select ename, sal, deptno from emp where empno<100;
    • You have defined index EMPNO_I1 on single column SCOTT.EMPNO, and defined concatenated index EMPNO_DEPT_I2 on columns SCOTT.EMPNO and SCOTT.DEPT (note, SCOTT.EMPNO is leading column).
      You must use the column SCOTT.EMPNO in the predicate list (WHERE clause) in order for the optimizer to consider either index:

      • CBO can use a Index Fast Full Scan (INDEX_FFS). The leading column of an index is not required for an INDEX_FFS to be performed. Note that an INDEX_FFS does not necessarily return the rows in order. Ordering is dependant on the order that the index blocks are read.
        See Note 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 Note 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 Note 67409.1, and Note 10577.1
    • Exceptions:

  • Are the indexed columns part of join predicates? e.g. emp.deptno= dept.deptno
      If Yes then:

      What type of join is used?

      Only a Nested loops join can allow index lookups on the inner table that are based solely on the join column(s):

      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.

    Does the join order allow index usage?

    Due to this limitation, the join order of the tables is important. The outer table of a nested loops join must have been vistied 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. For example: If there is an index on emp.deptno and, assuming ther eare no other predicates that relate to emp.deptno int hte query, emp is visited before dept, then no values are present that can be used to lookup rows in the emp.deptno index. The only way the index could be used is with a full index scan or a index fast full scan. In this case it is possible that a Full Table Scan (FTS) will cost less and be chosen instead. The RBO would not even consider using the index.

  • Are the indexed columns part of an IN list or multiple OR’s? e.g. emp.deptno IN (10,23,34,….)
      It is possible that the query has been transformed in to something that cannot use an index. See Note 62153.1
  • Are the indexed columns modified by functions?
      Indexes cannot be used on columns modified by functions. Oracle 8i adds function based  indexes.
  • 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.
  • 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? e.g. Index fast full scan 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 Note 62339.1 for more information.

@ PAA content management, action #23093

    • Since Oracle8i indexes alternatively can be defined with ascending or decending 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 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 Note 35934.1 for more details.
    • Remember that the CBO may choose a different index because the costs indicate that this is appropriate. The RBO uses a rigid system of rules to determine what access method to use.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 Note 44961.1 TECH: Comments on Frequency of using ANALYZE

@ PAA content management, action #23093

    • 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 is my querries slow since upgrading the database” can be found in note <160089.1>
  • Does the index have the same rank or cost as another index?
      If there is a choice between equally ranked access methods, then the RBO uses the order in the row cache to decide on which index to use (with equally ranked tables it uses from clause order from right to left). With equally costed indexes, the CBO uses the name of the index in ascii order. Note that this is unlikely to be a common occurrence.
      See Note 73167.1 and Bug 644757
  • Is the index unselective?
      • It may not be a good idea to use it anyway…
        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.
        • gather more accurate stats. See Note 44961.1 TECH: Comments on Frequency of using ANALYZE
          consider gathering column statistics where column data is not uniform
          use hints. See Note 29236.1 QREF: SQL Statement HINTS and Note 50607.1 How to specify an INDEX Hint
      • Possible workarounds:

    • The index is unselective The column data does not have a uniform distribution The optimizer statistics are inadequate making indexes appear unselective

  • Are the indexed columns NULLable?
      • 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 Note 28934.1
        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 Note 70067.1 All about Bitmap Indexes, or Oracle Concepts Database Concepts Manual.
    • Indexes do NOT store NULL values unless the index is concatenated (i.e. multi-column indexes), or it is a Bitmap index.

  • 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 Note 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 dependant on the query that is sent to the remote site. Often, with RBO, the query sent to the remote site does not contain indexed predicates. this can often result in a FTS. 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 Note 68809.1 Distributed Queries
  • Is parallel query (PQO) involved?
      Indexes cannot be accessed in parallel (other than using Index fast full scan). If PQO is involved, then the CBO may choose to use a parallel FTS instead of indexes if the FTS costs less. Remember that after 7.3, PQO forces use of the CBO which may not choose to use an index based on the cost information available. See Bug 502641
  • 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 Note 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 Note 68992.1 Predicate Selectivity
  • Index hints don’t work
    FIRST_ROWS Likely to promote the use of indexes
    ORDERED Forces the join order of a query
    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
  • Deletes do not necessarily free up allocated index space
      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.
  • Are partition views involved?
      Is the partition view setup correctly? See Note 28426.1 Partition Views and the use of Indexes (7.1 & 7.2) and Note 43194.1 Partition Views in 7.3: Examples and Tests
  • 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: Note 30779.1 Init.ora Parameter “NLS_SORT” Reference, and Note 227335.1 Linguistic Sorting – Frequently Asked Questions (section 4.)
 » 如果喜欢可以: 点此订阅本站
分类: 数据库 标签: ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.