Oracle不使用索引的几种情况列举

113次阅读

共计 15596 个字符，预计需要花费 39 分钟才能阅读完成。

本文介绍了几种不使用索引的情况，本文实验的 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;

Table created.

zx@ORCL>select count(*) from t;

COUNT(*)
———-
99999

zx@ORCL>create index idx_t on t(x,y);

Index created.

zx@ORCL>exec dbms_stats.gather_table_stats(user,’T’,cascade=>true);

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
–where 条件使用 y =5
zx@ORCL>select * from t where y=5;

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 15 | 80 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 15 | 80 (2)| 00:00:01 |
————————————————————————–

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);

Index created.

zx@ORCL>exec dbms_stats.gather_table_stats(user,’T1′,cascade=>true);

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
zx@ORCL>select * from t1 where object_id=42;

Execution Plan
———————————————————-
Plan hash value: 4072187533

————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 100 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“OBJECT_ID”=42)
filter(“OBJECT_ID”=42)

INDEX SKIP SCAN 步骤告诉 Oralce 要跳跃式扫描这个索引，查询 GENDER 值有改变的地方，并从那里开始向下读树，然后在所考虑的各个虚拟索引中查询 OBJECT_id=42。如果大幅增加 GENDER 的可取值，如下：
zx@ORCL>alter table t1 modify GENDER varchar2(2);

Table altered.

zx@ORCL>update t1 set gender=(chr(mod(rownum,1024)));

84656 rows updated.

zx@ORCL>commit;

Commit complete.

zx@ORCL>exec dbms_stats.gather_table_stats(user,’T1′,cascade=>true);

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
zx@ORCL>select * from t1 where object_id=42;

Execution Plan
———————————————————-
Plan hash value: 1601196873

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 101 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 101 | 344 (1)| 00:00:05 |
————————————————————————–

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

——————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | SELECT STATEMENT | | 1 | 153 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 99999 | 153 (1)| 00:00:02 |
——————————————————————-

zx@ORCL>alter table t modify y not null;

Table altered.

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

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1000 | 34000 | 153 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1000 | 34000 | 153 (1)| 00:00:02 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(MOD(“X”,999)=1)

zx@ORCL>create index idx_t_f on t(mod(x,999));

Index created.

zx@ORCL>exec dbms_stats.gather_table_stats(USER,’T’,cascade=>true);

PL/SQL procedure successfully completed.

zx@ORCL>select * from t where mod(x,999)=1;

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);

Table created.

zx@ORCL>insert into t2 values(‘5’,sysdate);

1 row created.

zx@ORCL>commit;

Commit complete.

zx@ORCL>exec dbms_stats.gather_table_stats(USER,’T2′,cascade=>true);

PL/SQL procedure successfully completed.

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: 1513984157

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 12 | 3 (0)| 00:00:01 |
————————————————————————–

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

————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |
————————————————————————————-

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;

Table created.

zx@ORCL>exec dbms_stats.gather_table_stats(USER,’T3′,cascade=>true);

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
– 运行一个查询查询相对较少的数据
zx@ORCL>select count(y) from t3 where x<50;

Execution Plan
———————————————————-
Plan hash value: 1961899233

———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| SYS_C0017451 | 49 | 245 | 2 (0)| 00:00:01 |
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“X”<50)
– 运行一个查询查询相对较多的数据
zx@ORCL>select count(y) from t3 where x<50000;

Execution Plan
———————————————————-
Plan hash value: 463314188

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 30 | 117 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T3 | 50000 | 1464K| 117 (1)| 00:00:02 |
—————————————————————————

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

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139779.htm

正文完

星哥说事-微信公众号