索引为什么会失效？总的来说有两大点：
第一，完全失效，即该表的索引不可用。原因：当某些操作导致数据的rowid改变， 索引就会完全失效。
那什么时候会导致rowid改变使得索引unuseable或者invalid呢？一般有以下几种情况：
alter index
move table
sqlldr direct=y + 主键重复
(查看user_indexes的status来确定用户索引状态)
-- alter indextrain@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
Copyright (c) 1982, , 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代价很大等等，日后总结补充。
