往期回顾 

    论索引影响性能的一面①索引的各种开销 

2.2 索引使用失效

2.2.1 索引逻辑失效

（1）用索引反而代价变得更高

这个道理比较简单，如果应用索引范围检索数据，返回大量记录且几乎是所有的记录，这时候用索引肯定有错，索引范围查询访问一般适合返回少量记录的情况，否则用全表扫描或者全索引扫描就可以。

（2）发生索引列的类型转换

在表字段设计的时候有一个非常重要的原则，什么类型的字段存什么类型的值，否则就会发生类型转化，具体请看如下例子：

drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonly
select * from t_col_type where id=6;
执行计划
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    36 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_COL_TYPE |     1 |    36 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------
   1 - filter(TO_NUMBER("ID")=6)
统计信息
--------
          0  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

实际上只有如下写法才可以用到索引，这个很不应该，如果什么类型的取值就设置什么样的字段，把id字段类型改为Number，就顺畅了，如下：

select * from t_col_type where id='6';
执行计划
--------
| Id | Operation                   | Name       |Rows  |Bytes |Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0| SELECT STATEMENT            |            |    1 |   36 |    2   (0)| 00:00:01 |
|   1|  TABLE ACCESS BY INDEX ROWID| T_COL_TYPE |    1 |   36 |    2   (0)| 00:00:01 |
|*  2|   INDEX RANGE SCAN          | IDX_ID     |    1 |      |    1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
   2 - access("ID"='6')
统计信息
--------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

（3）对索引进行了各种运算

对索引列进行了各种运算，详见后面的案例部分。

2.2.2 索引物理失效

（1）long列调整导致索引失效

环境准备（建表，建long字段）：

drop table t purge;
create table t (object_id number,object_name long);
create index idx_object_id on t(object_id);
insert into t values (1,'ab');
commit;
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS   INDEX_NAME
-------- ---------------
VALID    IDX_OBJECT_ID

接下来将long修改为clob，发现索引失效了，必须要重建索引，如下：

alter table T modify object_name clob;
set autotrace off
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS   INDEX_NAME
-------- --------------
UNUSABLE IDX_OBJECT_ID
alter index  idx_object_id rebuild;
set autotrace off
select t.status,t.index_name from user_indexes t where index_name='IDX_OBJECT_ID';
STATUS   INDEX_NAME
-------- ---------------
VALID    IDX_OBJECT_ID

（2）move操作会导致索引失效

Move 是一个危险系数非常高的操作，它可以收缩表降低高水平位，却会导致索引失效，因而需要重建索引，请看下面例子：

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table t modify object_id not null;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME                    STATUS
--------------------------------------
IDX_OBJECT_ID                 VALID
alter table t move;
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME                     STATUS
--------------------------------------
IDX_OBJECT_ID                 UNUSABLE

（3）分区表导致索引失效的操作

这在前面已经描述过了，这里就不再重复了，请读者自行回到前面的章节进行复习。归纳如下：

truncate分区会导致全局索引失效，不会导致局部索引失效。如果对truncate 增加update global indexes，则全局索引不会失效。

drop分区会导致全局索引失效，局部索引因为drop分区，所以也不存在该分区的局部索引了。如果对drop分区增加update global indexes，全局索引不会失效。

split分区会导致全局索引失效，也会导致局部索引失效。如果对split分区增加update global indexes，则全局索引不会失效。

add 分区不会导致全局索引失效，也不会导致局部索引失效。

exchange会导致全局索引失效，不会导致局部索引失效。如果对exchange分区增加update global indexes，则全局索引不会失效。

重要结论：

所有的全局索引，只要用到update global indexes ,都不会失效，其中add分区甚至不需要增加update global indexes都可以生效。

局部索引的操作都不会失效，除了split分区。切记split分区的时候，要将局部索引进行rebuild。

 索引失效分析

失效模式 说明

逻辑失效 用索引反而代价变得更高 索引范围查询访问一般适合返回少量记录的情况，如果返回大量记录，用索引反而更慢！

发生索引列的类型转换 select * from t_col_type where id=6;

1 - filter(TO_NUMBER("ID")=6)

对索引进行了各种运算 select * from t where object_id/2=:id;

物理失效 long列调整导致索引失效

alter table T modify object_name clob;
select t.status,t.index_name from user_indexes t where index_name= 'IDX_OBJECT_ID';
STATUS       INDEX_NAME
--------  --------------
UNUSABLE     IDX_OBJECT_ID

move操作会导致索引失效

alter table t move;
select index_name,status from user_indexes where index_name= 'IDX_OBJECT_ID';
INDEX_NAME                     STATUS
--------------------------------------
IDX_OBJECT_ID                 UNUSABLE

分区表导致索引失效的操作 在关于表设计的章节中有过详细介绍

2.3 索引取舍控制

2.3.1 避免表交叉重复建立索引

假如t表有nbr、area_code两列的联合索引，单列的nbr索引就显得多余，因为nbr、area_code索引可以用在单列nbr索引上，具体如下：

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id,object_type);
set autotrace traceonly
set linesize 1000
--以下就能用到索引，因为object_id列是前缀
select * from t where object_id=19;
执行计划
--------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   207 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |   207 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
统计信息
--------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1392  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2.3.2 删除系统从未用到的索引

环境搭建，建表建索引并完成某列索引的监控：

drop table t purge;
create table t as select * from dba_objects;
create index idx_t_id on t (object_id);
alter index idx_t_id monitoring usage;
select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED       START_MONITORING          END_MONITORING
---------- ---------- ---------- ---------- ------------------------- ---------------
IDX_T_ID   T          YES        NO         12/14/2015 07:44:36

接下来继续执行一个用索引的查询，然后再观察USED字段，发现索引被用过：

set autotrace traceonly
select * from t where object_id=10;
set autotrace off
select * from v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED       START_MONITORING          END_MONITORING
---------- ---------- ---------- ---------- ------------------------- ---------------
IDX_T_ID   T          YES        YES        12/14/2015 07:44:36

停止对索引的监控，观察v$object_usage状态变化，发现MONITORING的值为NO，且END_MONITORING记录了停止监控的时间，如下：

alter index idx_t_id nomonitoring usage;
INDEX_NAME TABLE_NAME MONITORING USED       START_MONITORING          END_MONITORING
-------------------------------------------------------------------------------------
IDX_T_ID      T       NO         YES      12/14/2015 07:44:36     12/14/2015 07:46:45

 2.3.3  组合列过多的索引很可疑

组合索引一般不宜过多，如果组合索引列达到4个以上，那这个索引本身就很大，就不一定高效。另外，索引更新也会出现比较大的性能问题。

未完待续...

论索引影响性能的一面③ 索引开销与经典案例销

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列