idea 很卡 后台编制索引 idea的indexing加载很慢

Diagnosing and Understanding Why a Query is Not Using an Index (Doc ID 67522.1)

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.   Oracle不必仅仅因为存在索引而使用索引。如果查询要求查询表中的每一行（例如，在表联接中），那么为什么要查询索引中的所有行以及表中的所有行？在这种情况下，忽略索引可提高性能。优化器基于统计信息做出决策，该统计信息涉及所有查询的各种访问方法（包括索引）的适用性，并选择最佳查询。

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?   查询(谓词列表)的where子句中是否将索引的 索引列/前导列 作为单个表(non-join)的谓词？ 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.)  如果不是，那么请记住至少要在谓词列表中使用索引列才能在查询中使用索引（但请参见下面的“Skip Scan”。）
  
  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):  示例：您已经在单列EMP.EMPNO上定义了索引EMPNO_I1，并且在EMP.EMPNO和EMP.DEPT列上定义了组合索引EMPNO_DEPT_I2（EMP.EMPNO是前导列）。为了使优化器考虑两个索引，必须在谓词列表（WHERE子句）中使用列EMP.EMPNO：

SELECT ename, sal, deptno FROM emp WHERE empno<100;

• 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:  CBO可以使用索引快速全扫描（INDEX_FFS），只要索引包含查询所需的所有列，并且索引键中的至少一列具有NOT NULL约束即可。要执行INDEX_FFS，不需要索引的前列。请注意，使用INDEX_FFS不一定按排序顺序返回行。顺序取决于读取索引块的顺序，并且只有在使用'order by'子句的情况下，才保证按排序顺序返回行。

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:   CBO可以使用索引跳跃扫描（INDEX_SS）。要执行INDEX_SS，不需要索引的前导列

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.  CBO可以选择使用索引以避免排序。为此，索引列将需要位于order by子句中
  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和dept表的deptno列之间的联接：

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.  例如：如果使用上面的"emp.deptno = dept.deptno"谓词将EMP联接到DEPT，并且在EMP.DEPTNO上有一个索引（假设查询中没有其他与EMP.DEPTNO相关的谓词） ，如果在DEPT之前访问了EMP，则那时没有行值可用于在EMP.DEPTNO索引中查找行，因为我们尚未访问任何提供此类查找键的对象。使用此连接顺序，唯一可以使用索引的方法是索引全扫描或索引快速全扫描（读取整个索引）。在这种情况下，全表扫描（FTS）的成本可能会更低，因此会被选择。
• Are the indexed columns part of an IN list or multiple OR's?   索引列是IN列表的一部分还是多个OR？ 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
  https://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 run-time 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:  如果要比较的两个值的数据类型不同，则Oracle必须对其中一个值实施类型转换，以进行比较。这称为隐式类型转换。通常，当开发人员在字符列中存储数字时，这会引起问题。在运行时，oracle被迫转换其中一个值，并且（由于固定规则）在索引的字符列周围放置一个to_number。将任何函数添加到索引列都会阻止使用索引。Oracle必须执行此类型转换的事实表明该应用程序存在设计问题。由于转换是对“每行检索”执行的，因此也会导致性能下降 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:   例如，是否使用索引快速全扫描而不是索引范围扫描？优化器可能已经选择了所需的索引，但对于用户而言，最好使用其他扫描方法。在这种情况下，请使用INDEX_FFS，INDEX_ASC和INDEX_DESC的Hint来强制您需要的扫描类型 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.  索引可以按升序或降序排序。Oracle将降序索引视为基于函数的索引，因此与用于默认升序排序的执行计划相比，可以使用不同的执行计划。通过检查执行计划，您不会看到将使用默认的升序还是降序排序，因此还要检查视图DBA_IND_COLUMNS的'DESCEND'列。
• Are the indexed columns NULLable?   索引列是否可以为NULL？ 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:  除非是组合索引（即多列索引）或是位图索引，否则索引不会存储NULL值。对于组合索引，只有在至少填充了索引列之一的情况下才存储NULL。组合索引中的尾随NULL被存储。如果所有索引列均为NULL，则不存储行。由于索引中缺少NULL值，因此可能需要返回NULL值（例如count）的操作无法使用索引。这是因为优化器无法保证仅使用索引就可以检索必要的信息。使用NOT IN谓词和NULL值也有一些注意事项 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:  位图索引允许存储NULL。因此，它们被认为是可空的，并且无论它们是否为NULL，优化器都可以使用它们。空索引对于某些类型的SQL语句（例如使用聚合函数COUNT的查询）可能很有用

SELECT count(*) FROM emp;

• For more information on Bitmap indexes, see:

Document 70067.1 All about Bitmap Indexes 

• Is NLS_SORT set to BINARY?   NLS_SORT是否设置为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:    如果NLS_SORT未设置为BINARY，将不使用索引。这是因为索引是根据键的二进制顺序（使用二进制值进行预排序）构建的。将NLS_SORT设置为BINARY以外的任何值都会导致排序使用全表扫描，而不考虑优化器选择的路径。有关NLS_SORT和索引使用的更多详细信息，请参考 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 11g第1版开始，您可以创建不可见索引或使现有索引不可见。除非您在会话或系统级别将OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE，否则优化器将忽略不可见索引。在DML语句期间，仍保留不可见索引 Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
  Database Administrator's Guide
  Understand When to Use Unusable or Invisible Indexes
  https://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:  CBO依靠准确，最新和完整的统计信息来确定特定查询的最佳访问计划。如果打算使用CBO，请确保已收集统计信息。在没有统计信息的情况下使用CBO将迫使CBO使用预定义的默认值，这些默认值不太可能产生良好的计划或促进应用程序使用索引 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:   请记住，CBO可能会选择其他索引，因为成本表明这是适当的。除了基本的表和索引统计信息外，还应为数据分布不均匀的列收集列统计信息。有关收集统计信息的建议，请参阅 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:  通常，应以使先前统计信息不准确的方式修改schema对象的数据或结构后，收集新的统计信息。例如，在将大量行加载到表中之后，收集有关行数的新统计信息。还建议在安装新的补丁集之后收集新的统计信息。当通过与当前执行相同的版本生成统计信息时，表访问最有效。有关“为什么自升级数据库以来我的查询为什么慢的更多信息”，请参见： 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:  对于成本相等的索引，CBO使用各种方法来打破常规，例如索引名称的字母顺序，完全匹配的索引（不适用于快速全扫描）的NDK (Number of Distinct Keys) 较大或叶块数较少的索引。请注意，这不太可能是常见的情况 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.  CBO假定列数据没有偏斜并且分布均匀。如果不是这种情况，则统计信息可能无法反映实际情况，并且由于列的整体具有非选择性质，因此可能无法为某些选择值选择索引。如果是这种情况，则应考虑创建直方图以记录更准确的列数据分布图，或者使用Hint。
• 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   使用hints/outlines等。请参阅 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:  可能在empno列上使用索引，因为所需的所有数据都存储在索引中，并且不需要回表查找。而在以下示例中

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.  需要进行回表查找，因为ename列未存储在索引中。随着查询中行数的增加，查找每个ename可能会变得消耗性能。
  
  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 high watermark 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 reorganize 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.  重组，截断或删除数据可能会也可能不会对查询产生成本影响。请记住，delete并不一定会释放对象分配的空间。此外，delete操作不会重置表的高水位线。Truncate。空块可能会使索引/表看起来比潜在的昂贵。Drop并重新创建对象将重新组织结构，并且可能会有所帮助（或可能造成阻碍）。当比较两个具有相同数据的不同系统的查询性能时，此问题通常最明显。
• 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.  某些参数的设置可能会影响各种对象的成本，从而导致索引受到青睐或以其他方式受到青睐。例如，在大多数情况下，建议将参数DB_FILE_MULTIBLOCK_READ_COUNT和OPTIMIZER_INDEX_COST_ADJ保留为默认值。除非针对运行的系统类型提出具体建议，否则，如果将感知索引成本降低或提高到不切实际的水平，则使用其他值可能会严重降低某些查询的性能。

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:  通常，索引不用于远程表。分布式查询中的索引使用取决于发送到远程站点的查询。CBO花费了远程访问的费用，并且将评估和比较在有或没有将索引谓词发送到远程站点的情况下的成本。因此，CBO应该就远程表上的索引使用情况做出更明智的决定。在包含相关谓词的远程站点上构建视图以强制使用索引，然后在本地查询中引用该视图通常会有所帮助 Document 68809.1 Distributed Queries
• Is Parallel Execution (PX) involved?  是否涉及并行执行（PX）？ 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:  出于成本考虑，可能存在某些情况，为什么不选择索引，因为它取决于子查询返回的值。可以通过Hint来强制使用索引 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:  CBO无法针对绑定变量针对相似或范围谓词生成准确的成本数字。这可能会导致无法选择索引 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:  如果表中的列包含可延迟的约束列（例如，NOT NULL约束）并且对该列进行了索引，则无论约束当前是延迟的还是显式设置为即时的，我们都不会考虑使用索引。例如：

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; <-- This sets all the deferred constraint to immediate

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT COUNT(1) FROM tdc;        <-- Will not use the index.

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":  在以下错误中概述了这一点，将其关闭为"not a bug"：

Bug 17895663  optimizer ignoring deferrable constraints even when not deferred and not in tx

• Index hints don't work  索引Hint不起作用 Remember to use table aliases. See:  记住要使用表别名 Document 69992.1 Why is my hint ignored?
  Document 50607.1 How to specify an INDEX HintUseful 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: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:212391.1 - Index Skip Scan Feature
NOTE:227335.1 - Linguistic Sorting - Frequently Asked Questions
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