轉個分割槽表Local索引Rebuild的總結

今天一個指令碼里面的一條build index老是出現ORA-00054: resource busy and acquire with NOWAIT specified 錯誤，錯誤本身比較容易理解，肯定是有排他鎖限制了對Partition Index的DDL操作。 那麼如何避免，以及使用分割槽索引需要注意的事情，都是需要去總結的,所以下面我結合實驗，簡單總結一下重建Local Index:

1. DML操作對index partition rebuild的影響。 如果在rebuild某一個分割槽的時候，有其他的程式在對這個index partition所對應的資料進行DML操作，rebuild會受影響:

kl@k02> create table t_part_1 (a number, b varchar2(10))
2 partition by range(a)
3 (partition t1 values less than (10),
4 partition t2 values less than (20),
5 partition t3 values less than (30),
6 partition tmax values less than (maxvalue));

Table created.

--- 不管是btree index還是bitmap index:
kl@k02> create index t_part_ind1 on t_part_1 (a) local;

Index created.

kl@k02> insert into t_part_1 values (21,'AAA');

1 row created.

< no commit>

--- 然後重新開一個session:
kl@k02> alter index t_part_ind1 rebuild partition t3;
alter index t_part_ind1 rebuild partition t3
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

2. 是否考慮加online這個引數，這個引數加上加上以後，除了rebuild過程中index 保持online狀態，Oracle還會在rebuild index之前等待所有DML操作結束，然後得到DDL鎖，開始rebuild.
kl@k02> alter index t_part_ind1 rebuild partition t3 online;

Index altered.

---- 如果不commit,上面的操作就會一直hold。

3. 如果是bitmap index, online 選項失效
kl@k02> alter index t_part_ind1 rebuild partition t3 online;
alter index t_part_ind1 rebuild partition t3 online
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online

4. 在split以後partition index的狀態是usable, 所以split以後不需要重建索引。
5. 在drop 分割槽 local index的時候，只能drop 整個index; 而rebuild index時只能一個一個分割槽來drop.
6. 如果Local index失效，在查詢時會出現如下問題:
kl@k02> select /*+ full */ * from t_part_1 where a=31;
select /*+ full */ * from t_part_1 where a=31
*
ERROR at line 1:
ORA-01502: index 'KL.T_PART_IND1' or partition of such index is in unusable state

---- 如果走全表掃描，則不會出現:
kl@k02> select /*+ full(t) */ * from t_part_1 t where a=31;

no rows selected

總結: Local Index對查詢效能的提高是顯而易見的，但他在維護過程中的成本也是比較高的，所以大家在使用Local Index的時候，需要仔細評估一下，如果不經常被使用(比如用MV,代替start-transformation)，最好避免使用。