索引失效对insert的影响

    当对某个字段建主键或者唯一约束时，会自动创建一个索引，这样将该index改为unusable时，约束还是enable状态，当有数据插入时，检查数据完整性时候，这个约束会去使用这个index，而这个index是unusable状态，就会报错。具体测试如下

1.创建主键约束索引，insert失败

QUOTE:
SQL> create table test(id number,name varchar2(10));

Table created.

SQL> alter table test add constraint pk_test primary key(id);

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        VALID

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        VALID

SQL>  insert into test  values (1,'sdfs');

1 row created.

SQL> commit;

Commit complete.

SQL> alter index PK_TEST unusable;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        UNUSABLE

SQL>  insert into test  values (2,'asdf');
insert into test  values (2,'asdf')
*
ERROR at line 1:
ORA-01502: index 'PRODUCT.PK_TEST' or partition of such index is in unusable state


2.创建不带约束的索引，insert成功

QUOTE:
SQL> drop table test purge;

Table dropped.

SQL> create table test(id number,name varchar2(10));

Table created.

SQL> create index pk_test on test(id);

Index created.

SQL>  select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        VALID

SQL>  insert into test  values (2,'asdf');

1 row created.

SQL> alter index PK_TEST unusable;

Index altered.

SQL>  select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        UNUSABLE

SQL>  insert into test  values (3,'dddffd');

1 row created


3.创建唯一约束索引，insert失败，这个时候，可以看一下约束的状态，是enable

QUOTE:
SQL> create table test(id number,name varchar2(10));

Table created.

SQL> alter table test add constraint pk_test unique (id);

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        VALID

SQL> insert into test  values (1,'sdfs');

1 row created.

SQL> commit;

Commit complete.

SQL> alter index PK_TEST unusable;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
PK_TEST                        UNUSABLE

SQL> insert into test  values (2,'asdf');
insert into test  values (2,'asdf')
*
ERROR at line 1:
ORA-01502: index 'PRODUCT.PK_TEST' or partition of such index is in unusable
state


select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from  user_constraints;

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
PK_TEST                        U TEST                           ENABLED


4.创建唯一约束索引，insert时，将约束关闭，insert成功

QUOTE:
SQL> drop table test purge;

Table dropped.

SQL> create table test(id number,name varchar2(10));

Table created.

SQL> alter table test add constraint pk_test unique (id);

Table altered.

SQL> alter index PK_TEST unusable;

Index altered.

SQL> insert into test  values (2,'asdf');
insert into test  values (2,'asdf')
*
ERROR at line 1:
ORA-01502: index 'PRODUCT.PK_TEST' or partition of such index is in unusable
state


SQL> alter table test disable constraint pk_test;

Table altered.

SQL> insert into test  values (2,'asdf');

1 row created.

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from  user_constraints;

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
PK_TEST                        U TEST                           DISABLED