本文介绍了几种不使用索引的情况，本文实验的Oracle数据库版本均为11.2.0.4 情况1：我们在使用一个B*树索引，而且谓词中没有使用索引的最前列。 如果这种情况，可以假设有一个表T，在T（x,y）上有一个索引。要做以下查询：select * from t where y=5。此时，优化器就不打算使用T（x,y）上的索引，因为谓词中不涉及X列。在这种情况下，倘若使用索引，可能就必须查看每个索引条目，而优化器通常更倾向于对T表做一个全表扫描。zx@ORCL>create table t as select rownum x,rownum+1 y,rownum+2 z from dual connect by level < 100000;
Predicate Information （identified by operation id）: ---------------------------------------------------
1 - filter（"Y"=5） --where条件使用x=5 zx@ORCL>select * from t where x=5;
Execution Plan ---------------------------------------------------------- Plan hash value: 1594971208
------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 3 （0）| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 15 | 3 （0）| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 2 （0）| 00:00:01 | -------------------------------------------------------------------------------------
Predicate Information （identified by operation id）: ---------------------------------------------------
2 - access（"X"=5）但这并不完全排除使用索引。如果查询是select x,y from t where y=5，优化器就会注意到，它不必全面扫描表来得到X或Y（x和y都在索引中），对索引本身做一个民快速的全面扫描会更合适，因为这个索引一般比底层表小得多。还要注意，仅CBO能使用这个访问路径。 zx@ORCL>select x,y from t where y=5;
Execution Plan ---------------------------------------------------------- Plan hash value: 2497555198
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 10 | 81 （2）| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| IDX_T | 1 | 10 | 81 （2）| 00:00:01 | ------------------------------------------------------------------------------
Predicate Information （identified by operation id）: ---------------------------------------------------
1 - filter（"Y"=5）另一种情况下CBO也会使用T（x,y）上的索引，这就是索引跳跃式扫描。当且仅当索引的最前列（在上面的例子中最前列是x）只有很少的几个不同值，而且优化器了解这一点，跳跃式扫描（skip scan）就能很好地发挥作用。例如，考虑（GEMDER,EMPNO）上的一个索引，其中GENDER可取值有M和F，而且EMPNO是唯一的。对于以下查询: select * from t where empno=5; 可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询，这说明从概念上讲这个查询会如下处理： select * from t where GENDER="M" and empno=5 union all select * from t where GENDER="F" and empno=5 它会跳跃式地扫描索引，以为这是两个索引：一个对应值M，另一个对应值F。 zx@ORCL>create table t1 as select decode（mod（rownum,2）,0,"M","F"） gender,all_objects.* from all_objects;
Table created.
zx@ORCL>create index idx_t1 on t1（gender,object_id）;
Predicate Information （identified by operation id）: ---------------------------------------------------
1 - filter（"OBJECT_ID"=42）情况2：在使用select count（*） from t查询（或类似的查询），而且在表T上有一个B*树索引。不过，优化器并不是统计索引条目，而是在全面扫描这个表（尽管索引比表要小）。在这种情况下，索引可能建立在一个允许有NULL值的列上。由于对于索引键完全为null的行不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的，如若不然，倘若它使用索引来统计行数，则可能会得到一个错误的答案。zx@ORCL>desc t; Name Null？ Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- X NUMBER Y NUMBER Z CHAR（23） zx@ORCL>select count（*） from t;
Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522
zx@ORCL>desc t Name Null？ Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- X NUMBER Y NOT NULL NUMBER Z CHAR（23）
zx@ORCL>select count（*） from t;
Execution Plan ---------------------------------------------------------- Plan hash value: 2371838348
----------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost （%CPU）| Time | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 80 （0）| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_T | 99999 | 80 （0）| 00:00:01 | -----------------------------------------------------------------------情况3： 对于一个有索引的列，做以下查询： select * from t where function（indexed_column）=value; 却发现没有使用indexed_colum上的索引。原因是这个列上使用了函数。如果是对indexed_column的值建立了索引，而不是对function（indexed_column）的值建索引。在此不能使用这个索引。如果愿意，可以另外对函数建立索引。 zx@ORCL>select * from t where mod（x,999）=1;
Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873
Execution Plan ---------------------------------------------------------- Plan hash value: 4125918735
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 3800 | 102 （0）| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3800 | 102 （0）| 00:00:02 | |* 2 | INDEX RANGE SCAN | IDX_T_F | 100 | | 1 （0）| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information （identified by operation id）: ---------------------------------------------------
2 - access（MOD（"X",999）=1）情况4： 考虑以下情况，已经对一个字符钱建立了索引。这个列只包含数据数据。如果使用以下语法来查询： select * from t where indexed_colum=5; 注意查询中的数字5是常数5（而不是一个字符串），此时就没有使用INDEXED_COLUMN上的索引。这是因为，前面的查询等价于以下查询： select * from t where to_number（indexed_column）=5; 我们对这个列隐式地应用了一个函数，如情况3所述，这就会禁止使用这个索引。 zx@ORCL>create table t2 （x char（1） constraint t2_pk primary key ,y date）;
Predicate Information （identified by operation id）: ---------------------------------------------------
1 - filter（TO_NUMBER（"X"）=5）
Note ----- - dynamic sampling used for this statement （level=2）可以看到，它会全面扫描表；另外即使我们对查询给出了以下提示： zx@ORCL>explain plan for select /*+ index（t2 t2_pk） */ * from t2 where x=5;
Explained.
zx@ORCL>select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3365102699
------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2 （0）| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 2 （0）| 00:00:01 | |* 2 | INDEX FULL SCAN | T2_PK | 1 | | 1 （0）| 00:00:01 | -------------------------------------------------------------------------------------
Predicate Information （identified by operation id）: ---------------------------------------------------
2 - filter（TO_NUMBER（"X"）=5）在此使用了索引，但是并不像我们想像中那样对索引完成唯一扫描（UNIQUE SCAN），而是完成了全面扫描（FULL SCAN）。原因从最后一行输出可以看出:filter（TO_NUMBER（"X"）=5）。这里对这个数据库列应用了一个隐式函数。X中存储的字符串必须转换为一个数字，之后才能与值5进行比较。在此无法把5转换为一个串，因为我们的NLS（国家语言支持）设置会控制5转换成串时的具体形式（而这是不确定的，不同的NLS设置会有不同的控制），所以应当把串转为数据。而这样一样（由于应用也函数），就无法使用索引来快速地查找这一行了。如果只是执行串与串的比较： zx@ORCL>explain plan for select * from t2 where x="5";
Explained.
zx@ORCL>select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3897349516
Predicate Information （identified by operation id）: ---------------------------------------------------
2 - access（"X"="5"）
14 rows selected.不出所料，这会得到我们期望的INDEX UNIQUE SCAN,而且可以看到这里没有应用函数。一定要尽可能地避免隐式转换。 还经常出现一个关于日期的问题，如果做以下查询： select * from t where trunc（date_col）=trunc（sysdate）; 而且发现这个查询没有使用DATE_COL上的索引，为了解决这个问题，可以对trunc（date_col）建立索引，或者使用区间比较运算符来查询（也许这是更容易的做法）。下面来看对日期使用大于或小于运算符的一个例子。可以认识到以下条件： trunc（date_col）=trunc（sysdate） 与下面的条件是一样的： date_col>= trunc（sysdate） and date_col<trunc（sysdate+1） 如果可能的话，倘若谓词中有函数，尽量不要对数据库列应用这些函数。这样做不仅可以使用更多的索引，还能减少处理数据库所需的工作。使用转换的条件查询时只会计算一次TRUNC值，然后就能使用索引来查找满足条件的值。使用trunc（date_col）=trunc（sysdate）时，trunc（date_col）则必须对整个表（而不是索引）中的每一行计算一次。 情况5： 另一种情况，如果使用了索引，实际上反而会更慢。Oracle（对于CBO而言）只会在合理地时候才使用索引。 zx@ORCL>create table t3 （x,y null,primary key （x） ） as select rownum x,object_name y from all_objects;
Predicate Information （identified by operation id）: ---------------------------------------------------
2 - filter（"X"<50000）这个例子显示出优化器不一定会使用索引，而且实际上，它会做出正确的选择。对查询调优时，如果发现你认为本该使用的某个索引实际上并没有用到，就不要冒然强制使用这个索引，而应该先做个测试，并证明使用这个索引后确实会加快速度（通过耗用时间和I/O次数来评判），然后再考虑让CBO就范（强制它使用这个索引）。总得先给出个理由吧。 情况6： 有一段时间没有分析表了。这些表起先很小，但等到查看时，它们已经增长得非常大。现在索引就有很有意义（尽管原先并非如此）。如果此时分析这个表，就会使用索引。 如果没有正确的统计信息，CBO将无法做出正确的决定。 以上介绍了6种不使用索引的情况，归根结底原因通常就是“不能使用索引，使用索引会返回不正确的结果”，或者“不应该使用，如果使用了索引，性能会变得很糟糕”。 参考: MOS文档：Diagnosing Why a Query is Not Using an Index （文档 ID 67522.1）Oracle Database 9i/10g/11g编程艺术：深入数据库体系结构（第2版）PDF http://www.linuxidc.com/Linux/2016-02/128078.htm本文永久更新链接地址
易网时代-编程资源站