状态为unusable的索引影响DML速度吗？

秋风No.1

原帖由 fish617 于 2008-7-16 18:27 发表
设置index为unusable时，要修改参数skip_unusable_indexes为TRUE，否则用到该索引时会报错.

如果index状态为UNUSABLE,insert数据的时候，会报错并拒绝，测试如下

show parameter skip

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE

使index不可用

alter index pk_test unusable

查看index状态

select index_name,status,table_name from user_indexes;

INDEX_NAME                     STATUS   TABLE_NAME
------------------------------ -------- ------------------------------
PK_TEST                        UNUSABLE TEST

开始insert

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

可见如果index状态是unusable，insert可能是无法完成的，这种情况在分区表中比较常见，如果建index的时候，是global index，在维护分区的时候，可能就会使index 状态为unusable

[ 本帖最后由 秋风No.1 于 2008-7-17 11:47 编辑 ]

dhhb

呵呵，老同事所见略同啊

fish617

原来如此,谢谢!原来被烂书给骗了,唉,尽信书不如无书!

fusm

靠，偶也叫书给骗了。

秋风No.1

原帖由 dhhb 于 2008-7-17 13:28 发表
呵呵，老同事所见略同啊

imtj

10g中吧index 设置成为unusable可以插入的，9I没有试过

秋风No.1

原帖由 imtj 于 2008-7-17 17:54 发表
10g中吧index 设置成为unusable可以插入的，9I没有试过

我测试的环境就是10G，你是用什么方法insert的？给大家共享一下吧

fusm

这个问题有必要仔细研究综合一下，说不定oracle自己都不明确呢。

imtj

SQL> drop table test_part1;

Table dropped

SQL>
SQL> create table test_part1 (id number(4),hire_date date)
  2  partition by range (id)
  3  (
  4  partition testpart1_1 values less than (100) tablespace part1,
  5  partition testpart1_2 values less than (200) tablespace part2,
  6  partition testpart1_3 values less than (300) tablespace part3,
  7  partition testpart1_4 values less than (maxvalue)
  8  );

Table created

SQL>
SQL> declare
  2  i int;
  3  begin
  4  for i in 1..600 loop
  5  insert into test_part1 values (i,sysdate);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed

SQL> create index test_part1_indx on test_part1(id)
  2  /

Index created

SQL> alter index test_part1_indx unusable;

Index altered

SQL>
SQL> declare
  2  i int;
  3  begin
  4  for i in 1..600 loop
  5  insert into test_part1 values (i,sysdate);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

秋风No.1

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

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

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

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

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

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

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

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

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