or导致索引失效的解决方法_索引失效原因总结

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

第一，完全失效，即该表的索引不可用。原因：当某些操作导致数据的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 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代价很大等等，日后总结补充。