索引失效原因总结

索引为什么会失效？总的来说有两大点：

 

第一，完全失效，即该表的索引不可用。原因：当某些操作导致数据的rowid改变， 索引就会完全失效。

 

那什么时候会导致rowid改变使得索引unuseable或者invalid呢？一般有以下几种情况：

alter index

move table

sqlldr direct=y + 主键重复

（查看user_indexes的status来确定用户索引状态）

-- alter index
train@HUIYI(10.222.19.112)> create table t01
  2  as select 1 col01 from dual;

Table created.

Elapsed: 00:00:00.11
train@HUIYI(10.222.19.112)> create index t01_01 on t01(col01);

Index created.

Elapsed: 00:00:00.07
train@HUIYI(10.222.19.112)> select index_name,status
  2    from user_indexes
  3   where table_name='T01';

INDEX_NAME           STATUS
-------------------- ----------------
T01_01               VALID

Elapsed: 00:00:00.09
train@HUIYI(10.222.19.112)> alter index t01_01 unusable;

Index altered.

Elapsed: 00:00:00.01
train@HUIYI(10.222.19.112)> select index_name,status
  2    from user_indexes
  3   where table_name='T01';

INDEX_NAME           STATUS
-------------------- ----------------
T01_01               UNUSABLE

Elapsed: 00:00:00.01
train@HUIYI(10.222.19.112)> alter index t01_01
  2  rebuild online;

Index altered.

Elapsed: 00:00:00.78
train@HUIYI(10.222.19.112)> select index_name,status
  2    from user_indexes
  3   where table_name='T01';

INDEX_NAME           STATUS
-------------------- ----------------
T01_01               VALID

Elapsed: 00:00:00.01

---

-- move table
train@HUIYI(10.222.19.112)> create table t02
  2  as select 1 col01 from dual;

Table created.

Elapsed: 00:00:00.07
train@HUIYI(10.222.19.112)> create index t02_01 on t02(col01);

Index created.

Elapsed: 00:00:00.09
train@HUIYI(10.222.19.112)> select index_name, status
  2    from user_indexes
  3   where table_name = 'T02';

INDEX_NAME           STATUS
-------------------- ----------------
T02_01               VALID

Elapsed: 00:00:00.09
train@HUIYI(10.222.19.112)> alter table t02 move tablespace tbs01;

Table altered.

Elapsed: 00:00:00.29
train@HUIYI(10.222.19.112)> select index_name, status
  2    from user_indexes
  3   where table_name = 'T02';

INDEX_NAME           STATUS
-------------------- ----------------
T02_01               UNUSABLE

Elapsed: 00:00:00.00
train@HUIYI(10.222.19.112)> alter index t02_01
  2  rebuild online;

Index altered.

Elapsed: 00:00:00.21
train@HUIYI(10.222.19.112)> select index_name, status
  2    from user_indexes
  3   where table_name = 'T02';

INDEX_NAME           STATUS
-------------------- ----------------
T02_01               VALID

Elapsed: 00:00:00.01

---

-- sqlldr
train@HUIYI(10.222.19.112)> create table t03
  2  (
  3  col01 number primary key
  4  )
  5  /

Table created.

Elapsed: 00:00:00.07
train@HUIYI(10.222.19.112)> insert into t03
  2  values(1);

1 row created.

Elapsed: 00:00:00.04
train@HUIYI(10.222.19.112)> commit;

Commit complete.

Elapsed: 00:00:00.00

C:\>sqlldr train/train control=data.ctl data=data.txt direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 30 12:33:46 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Load completed - logical record count 1.

train@HUIYI(10.222.19.112)> select * from t03;

     COL01
----------
         1
         1

Elapsed: 00:00:00.01
train@HUIYI(10.222.19.112)> select index_name, status
  2    from user_indexes
  3   where table_name = 'T03';

INDEX_NAME           STATUS
-------------------- ----------------
SYS_C006393          UNUSABLE

Elapsed: 00:00:00.07
train@HUIYI(10.222.19.112)> alter index sys_c006393
  2  rebuild online;
alter index sys_c006393
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

 

第二，sql语句查询查询过程失效。这种情况表现的现象是再查询索引信息时候并没有unuseable，可是在跟踪sql语句执行过程中并没有用到索引。引起这种失效的原因很多，比如sql语句本身语法不合理、该表频繁插入更新导致oracle计算cost代价很大等等，日后总结补充。