在INDEX 處於UNABLE 狀態下插入資料

在環境上把一個INDEX 處於unusable狀態：
這是一個9IR2的環境

SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select * from dba_objects;

34084 rows created.
SQL>  create index i_test_1 on test(object_id);

Index created.

SQL> alter index i_test_1 unusable;

Index altered.

這時對TABLE 進行 dml操作時，如果dml語句沒有對test.object_id 自動進行操作時，語句可以正常操作。
但如果包括了test.object_id 欄位，sql會報錯：
SQL> update test set OBJECT_ID   = 2000;
update test set OBJECT_ID   = 2000
*
ERROR at line 1:
ORA-01502: index 'SYS.I_TEST_1' or partition of such index is in unusable state

SQL> insert into test select * from dba_objects;
insert into test select * from dba_objects
*
ERROR at line 1:
ORA-01502: index 'SYS.I_TEST_1' or partition of such index is in unusable state

SQL>  insert /*+ append */ into  test select * from dba_objects;
  2 
SQL>  insert /*+ append */ into  test select * from dba_objects;
 insert /*+ append */ into  test select * from dba_objects
                            *
ERROR at line 1:
ORA-26028: index SYS.I_TEST_1 initially in unusable state
可以看到在9I下，如果對含有UNUSABLES INDEX 欄位的table進行dml操作，ORACLE是不允許的。

要解決這個問題，那需要在當前session中更改SKIP_UNUSABLE_INDEXES 為true。
執行的指令碼：
SQL> alter session set SKIP_UNUSABLE_INDEXES =true;

Session altered.
SQL>  insert /*+ append */ into  test select * from dba_objects;

34085 rows created.
SQL>  select status from user_indexes where index_name='I_TEST_1';

STATUS
------------------------
UNUSABLE

SQL> alter index I_TEST_1 rebuild;

Index altered.

SQL> select status from user_indexes where index_name='I_TEST_1';

STATUS
------------------------
VALID

在10g下， oracle可以忽略DML 語句中含有unusable index的欄位。原因是skip_unusable_indexes 引數從9I的會話級別的
上升到INSTANCE級別的。
而且預設值為TRUE.

SQL> select * from v$version where rownum < 2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

SQL> show parameter skip

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

SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select * from dba_objects;

50008 rows created.

SQL> create index i_test_1 on test(object_id);

Index created.

SQL> alter index i_test_1 unusable;

Index altered.

SQL>  insert /*+ append */ into  test select * from dba_objects;

50009 rows created.
SQL>  select status from user_indexes where index_name='I_TEST_1';

STATUS
--------
UNUSABLE

SQL> alter index I_TEST_1 rebuild;

Index altered.

SQL> select status from user_indexes where index_name='I_TEST_1';

STATUS
--------
VALID

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-573313/，如需轉載，請註明出處，否則將追究法律責任。