10g新增初始化引數SKIP_UNUSABLE_INDEXES

這個10.1就增加的新特性，是最近才發現的。

公司中的新人問起索引失效對錶影響，於是隨手做了個例子，才發現10g中已經改變了9i中的預設方式：

SQL> create table t_index (id number, name varchar2(30));

Table created.

SQL> create index ind_t_id on t_index(id);

Index created.

SQL> create index ind_t_name on t_index(name);

Index created.

SQL> insert into t_index values (1, 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t_index move;

Table altered.

SQL> select index_name, status from user_indexes where table_name = 'T_INDEX';

INDEX_NAME                     STATUS
------------------------------ --------
IND_T_ID                       UNUSABLE
IND_T_NAME                     UNUSABLE

SQL> insert into t_index values (2, 'b');

1 row created.

當這個插入語句成功後，我發現自己做的例子居然和預期產生了差異，在我的印象中，這個插入語句是應該報錯的。

隨後馬上做了三件事情：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select index_name, status from user_indexes where table_name = 'T_INDEX';

INDEX_NAME                     STATUS
------------------------------ --------
IND_T_ID                       UNUSABLE
IND_T_NAME                     UNUSABLE

SQL> show parameter index

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
skip_unusable_indexes                boolean     TRUE

首先第一件事情就是堅持當前資料庫的版本，我可以確定在9i，最後一個INSERT會由於索引的狀態不可用而導致錯誤，而當前可以執行成功，因此首先確定版本問題。

在確認是10.2版本後，隨後又堅持了一下索引的狀態，看看是否插入導致Oracle重建了索引。而當前索引的狀態並未發生變化，說明Oracle在執行插入時，跳過了這些不可用的索引。

於是順理成章的就是第三個動作，堅持包含index關鍵字的初始化引數，看看是否是10g中存在了一個類似的初始化引數，使得索引在不可用狀態下，基表同樣可以進行DML操作。

查詢果然發現了這個引數，而且從名稱以及設定的值來看，確認就是要找的初始化引數：

SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL> insert into t_index values (3, 'c');
insert into t_index values (3, 'c')
*
ERROR at line 1:
ORA-01502: index 'TEST.IND_T_ID' or partition of such index is in unusable state

SQL> alter index ind_t_id rebuild;

Index altered.

SQL> alter index ind_t_name rebuild;

Index altered.

SQL> insert into t_index values (3, 'c');

1 row created.

SQL> commit;

Commit complete.

將初始化引數設定為FALSE後，資料庫的行為果然表現的和9i中一致。

查詢了一下Oracle的文件，發現在10.1中Oracle已經引入了這個初始化引數。這個引數的引入可以避免由於表MOVE或其他DDL導致的索引失效，從而影響到整個表的可用性，同時這個引數的引入也會存在問題，它使得本來可以很快發現的問題埋藏的很深，甚至可能造成嚴重的效能隱患。

當然Oracle也意識到這一點，在告警日誌中可以看到這些的提示資訊：

Sat Dec 31 10:53:33 2011
Some indexes or index [sub]partitions of table TEST.T_INDEX have been marked unusable

這同樣也說明，任何一個引數都有兩個方面，如果設定引數後只有優點而沒有缺點，Oracle也就沒有必要讓使用者去選擇了。