文档内容
适用于: Oracle Database - Standard Edition - 版本号 8.1.7.4 和更高版本号
Oracle Database - Personal Edition - 版本号 8.1.7.4 和更高版本号
Oracle Database - Enterprise Edition - 版本号 8.1.7.4 和更高版本号
本文档所含信息适用于全部平台
用途
这篇文章用来解答以下的问题:为什么我的索引没有被使用?
排错步骤
“为什么索引没有被使用 ”是一个涉及面较广的问题。有非常多种原因会导致索引没有被使用。
以下是一些非常实用的检查列表。请点击以下链接来查看文章的详细内容:
高速检查
索引本身的问题
索引列或者索引的前置列是否在单表(non-join)查询的 Where 条件中(predicate list)? 假设不是,至少须要索引前置列在查询谓词列表中,查询才干使用索引。(例外:请见以下的 Skip Scan)。 演示样例: 在列 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;
例外:
索引列是否用在连接谓词中(join predicates)? 比如。以下这个连接谓词定义了怎样在表 emp 和 dept 的 deptno 列上做连接:
假设索引列是连接谓词的一部分,那么查询在运行时使用了哪种类型的连接?
仅仅有嵌套循环连接(Nested loops join)同意索引在内部表中仅基于连接列进行查找。 另外,连接的顺序(join order)是否同意使用索引? 一个嵌套循环连接的外部表必须已经訪问过,才干够在内部表中使用索引。查看 explain plan,以确定哪些訪问路径已经使用。
因为这个限制,表的连接顺序是非常重要的。 比如:如果我们通过"emp.deptno = dept.deptno"来对 EMP 和 DEPT 做连接,而且在 EMP.DEPTNO 有一个索引。并如果查询中没有与 EMP.DEPTNO 相关的其它谓词,EMP 是在 DEPT 前被訪问,然后没有值可用于在 EMP.DEPTNO 索引中查询。
在这样的连接顺序下,要想使用这个索引我们仅仅能使用全索引扫描或索引高速全扫描。在这样的情况下。全表扫描(FTS)的成本可能更小。
索引列在 IN 或者多个 OR 语句中? 比方:
emp.deptno IN (10,23,34,....)
或
emp.deptno = 10
OR emp.deptno = 23
OR emp.deptno = 34
....
这样的情况下查询可能已经被转化为不能使用索引的语句。请參照:
索引列是否被函数改动? 索引不能用于被函数改动的列。函数索引(function based indexes)能够用来解决问题。
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
隐式类型转换(implicit type conversion)是什么? 假设进行比較的两个值的数据类型不同,则 Oracle 必须将当中一个值进行类型转换使其可以比較。这就是所谓的隐式类型转换。通常当开发者将数字存储在字符列时会导致这样的问题的产生。Oracle 在执行时会强制转化当中一个值,(因为固定的规则)在索引字符列使用 to_number。因为加入函数到索引列所以导致索引不被使用。实际上,Oracle 也仅仅能这么做,类型转换是一个应用程序设计因素。因为转换是在每行都进行的,这会导致性能问题。
详见:
Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
是否在语义(semantically)上无法使用索引? 出于对查询总体成本的考虑。一个成本较低的运行计划中可能是无法使用索引的。某索引可能已经被考虑在某种连接排序及方法中。可是成本最低的那个运行计划中却无法从“语义”角度使用该索引。 错误类型的索引扫描? 比如:高速全索引扫描而不是索引范围扫描 这可能是优化器选择了所需的索引,但却使用了客户不希望的扫描方法。在这样的情况下,利用 INDEX_FFS,INDEX_ASC 和 INDEX_DESC 提示来强制使用须要的扫描类型。 请參照:
我们还能够定义索引的排序顺序为递增或递减。Oracle 对待降序索引就好像它是基于函数的索引。因此与缺省使用的升序的运行计划不同。通过查看运行计划,您看不到使用升序或降序,须要额外检查视图 DBA_IND_COLUMNS 的'DESCEND'列。 是否索引列为可空? 索引不存储 NULL 值。除非该索引为联合索引(即多列索引),或者它是一个位图索引。 仅仅有至少有一个索引列有值。联合索引才存储空值。联合索引中尾部的空值也会被存放在索引中。假设全部列的值都为空,这行将不会存储在索引中。由于索引中缺乏 NULL 值,那么一些结果中可能会返回 NULL 值(如count)的操作可能会被禁用索引。这是由于优化器不能保证在单独使用索引时能够获得准确的信息。关于使用 NOT IN 和 NULL 的其它一些考虑,请參考
位图索引同意存储空值。因此优化器会使用这些索引,不管它们的结果可信与否。索引上的空值有时非常实用,特别对于某些类型的 SQL 语句,如与聚合函数 COUNT 查询。
演示样例:
SELECT count(*) FROM emp;
位图索引的很多其它信息请參考
NLS_SORT是否设置为二进制(BINARY)? 假设 NLS_SORT 未设置为二进制,索引将不会被使用。这是由于索引是基于 Key 值的二进制顺序来建立的(pre-sorted使用二进制值)。不管优化器设置为何种方法。NLS_SORT 不是二进制时。将使用全表扫描,。很多其它关于NLS_SORT和索引的使用,请參考:
是否使用的是不可见索引(invisible indexes)? 从 Oracle Database 11g Release 1開始,您能够创建不可见索引或将一个已经存在的索引标记为不可见。Optimizer 不会考虑不可见索引,除非在 session 或 system 级将參数 OPTIMIZER_USE_INVISIBLE_INDEXES 设置为 TRUE。DML 操作还是会维护这些不可见索引的。详见:
优化器和成本计算相关问题
是否存在准确且合适的统计信息(Statistics)? CBO 依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳运行计划。假设使用 CBO,请确保统计信息已经收集。假设没有统计信息, CBO 将使用提前定义的统计信息。这样是非常可能不会产生良好的计划或让应用程序使用索引。请參照:
Document 754931.1 Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
其他问题
是否使用了视图/子查询? 查询涉及到视图或者子查询时可能会被改写,导致不使用索引(虽然该改写的目标之中的一个是扩展很多其它的訪问路径)。这些改写(rewrite)一般来说都是合并(merging)操作。请查看
是否存在远程表(remote table)? 通常远程表不会使用索引。索引在分布式查询中的使用依赖于被发送到远程的查询。
CBO 将评估远程訪问的成本,并评估比較发送或者不发送索引的谓词到远程网站的成本。因此。CBO 能够做出有关远程表上使用索引的更加明智的决定。一个很有效的方法就是,在远程建立包括相关谓词的视图并强制使用索引。之后在本地查询中使用这个视图。 请參考
是否使用并行运行(PX)? 在并行运行时索引的採用比在串行运行((serial execution))时更加严格。一个高速检測的方法就是禁用并行。然后查看该索引是否被使用。
是否是包括了子查询的Update语句? 在一些情况下,基于成本的考虑,索引没有被选使用是由于它依赖于一个子查询返回的值。这样的情况下,能够使用提示(hint)来强制使用索引。
请參考
Document 68084.1 Using hints to optimize an Update with a subquery that is not using an index on the updated table.
查询是否使用了绑定变量? CBO 对 like 或范围谓词的绑定变量不能产生准确的成本(cost)。这可能会导致索引不被选择。 请參考
查询是否引用了带有延迟约束的列? 假设一个表中的某一列上含有延迟约束(比方 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; <-- 将全部延迟约束置为马上使用 SET AUTOTRACE TRACEONLY EXPLAIN
SELECT COUNT(1) FROM tdc; <-- 索引不会被使用 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 | -------------------------------------------------------------------
这个现象在下面 bug 中记录。关闭为"not a bug":
Bug 17895663 optimizer ignoring deferrable constraints even when not deffered and not in tx
索引提示(hint)不工作 请使用表的别名。 请參考
实用的 hints:
FIRST_ROWS 相当于提示使用索引 ORDERED 强制查询的关联顺序(join order of a query)。Oracle 推荐使用 LEADING hint 由于它更好用。 LEADING 这个 hint 告诉 optimizer 先使用指定的表做连接。它比 ORDERED 更好用。
INDEX 强制使用索引扫描, 并禁用高速模式(INDEX_FFS) INDEX_FFS 强制使用高速索引扫描INDEX_FFS INDEX_ASC 强制使用升序的索引范围扫描(Ascending Index Range Scan) INDEX_DESC 强制使用降序的索引范围扫描(Descending Index Range Scan)
參见:
转载于:https://www.cnblogs.com/llguanli/p/8280317.html