狀態(tài)為unusable的索引影響DML速度嗎？

秋風(fēng)No.1

原帖由 fish617 于 2008-7-16 18:27 發(fā)表
設(shè)置index為unusable時(shí)，要修改參數(shù)skip_unusable_indexes為T(mén)RUE，否則用到該索引時(shí)會(huì)報(bào)錯(cuò).

如果index狀態(tài)為UNUSABLE,insert數(shù)據(jù)的時(shí)候，會(huì)報(bào)錯(cuò)并拒絕，測(cè)試如下

show parameter skip

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

使index不可用

alter index pk_test unusable

查看index狀態(tài)

select index_name,status,table_name from user_indexes;

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

開(kāi)始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

可見(jiàn)如果index狀態(tài)是unusable，insert可能是無(wú)法完成的，這種情況在分區(qū)表中比較常見(jiàn)，如果建index的時(shí)候，是global index，在維護(hù)分區(qū)的時(shí)候，可能就會(huì)使index 狀態(tài)為unusable

[ 本帖最后由 秋風(fēng)No.1 于 2008-7-17 11:47 編輯 ]

dhhb

呵呵，老同事所見(jiàn)略同啊

fish617

原來(lái)如此,謝謝!原來(lái)被爛書(shū)給騙了,唉,盡信書(shū)不如無(wú)書(shū)!

fusm

靠，偶也叫書(shū)給騙了。

秋風(fēng)No.1

原帖由 dhhb 于 2008-7-17 13:28 發(fā)表
呵呵，老同事所見(jiàn)略同啊

imtj

10g中吧index 設(shè)置成為unusable可以插入的，9I沒(méi)有試過(guò)

秋風(fēng)No.1

原帖由 imtj 于 2008-7-17 17:54 發(fā)表
10g中吧index 設(shè)置成為unusable可以插入的，9I沒(méi)有試過(guò)

我測(cè)試的環(huán)境就是10G，你是用什么方法insert的？給大家共享一下吧

fusm

這個(gè)問(wèn)題有必要仔細(xì)研究綜合一下，說(shuō)不定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

秋風(fēng)No.1

這個(gè)問(wèn)題看似矛盾，是因?yàn)檫@個(gè)索引字段是否未主鍵約束或唯一約束，當(dāng)對(duì)某個(gè)字段建主鍵或者唯一約束時(shí)，會(huì)自動(dòng)創(chuàng)建一個(gè)索引，這樣將該index改為unusable時(shí)，約束還是enable狀態(tài)，當(dāng)有數(shù)據(jù)插入時(shí)，檢查數(shù)據(jù)完整性時(shí)候，這個(gè)約束會(huì)去使用這個(gè)index，而這個(gè)index是unusable狀態(tài)，就會(huì)報(bào)錯(cuò)。具體測(cè)試如下

創(chuàng)建主鍵約束索引，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

創(chuàng)建不帶約束的索引，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

創(chuàng)建唯一約束索引，insert失敗，這個(gè)時(shí)候，可以看一下約束的狀態(tài)，是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

創(chuàng)建唯一約束索引，insert時(shí)，將約束關(guān)閉，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