分割槽表中全域性及本地分割槽索引什麼時候會失效及處理[final]

tolywang發表於2011-01-22


分割槽表中 local 索引的維護會在oracle 操作表分割槽的時候自動進行,需要注意的是global 索引,當global索引所在表執行alter table 涉及下列操作時,會導至該索引失效,需要重新建立:


 ADD PARTITION | SUBPARTITION
 COALESCE PARTITION | SUBPARTITION
 DROP PARTITION | SUBPARTITION
 EXCHANGE PARTITION | SUBPARTITION
 MERGE PARTITION | SUBPARTITION
 MOVE PARTITION | SUBPARTITION
 SPLIT PARTITION | SUBPARTITION
 TRUNCATE PARTITION | SUBPARTITION


因此,建議使用者在執行上述操作sql 語句後附加update indexes 子句,oracle
即會自動維護全域性索引,當然,需要注意這中間有一個平衡,你要平衡操作ddl 的
時間和重建索引哪個時間更少,以決定是否需要附加updateindexes 子句。

 

---------------------------------------------------------------------- 

 


執行alter table add partition 時未指定update indexes 子句:
a. 如果是range/list分割槽,其local 索引和global 索引不會受影響;
b. 如果是hash 分割槽,新加分割槽及有資料移動的分割槽的local 索引和global索引會被
置為unuseable,需要重新編譯。

在執行drop partition時如果沒有指定update indexes 子句,會導致glocal
索引的失效,對於local索引,刪除分割槽時對應的索引分割槽會被同時刪除,且
它分割槽的local 索引不會受到影響。


在執行split partition/subpartition 時,如果沒有指定update indexes 子
句,都會造成local 和global 索引的失效。不過如果你split partition/
subpartition 的是個空分割槽,或者沒有觸發任何資料移動或變化,那麼即使不加
update indexes,也不會影響到索引。當然,保險起見,建議你還是執行完之後,
查詢一下資料字典,確認一下當前索引的狀態。

 


下面簡單測試一下:

 

1. 建立一個Range分割槽表:

CREATE TABLE DFMS.TEST04
PARTITION BY RANGE(OBJECT_ID)

  PARTITION P1 VALUES LESS THAN (2000)
    TABLESPACE LOG_DATA, 
  PARTITION P2 VALUES LESS THAN (8000)
    TABLESPACE LOG_DATA, 
  PARTITION P3 VALUES LESS THAN (20000)
    TABLESPACE LOG_DATA, 
  PARTITION P4 VALUES LESS THAN (40000)
    TABLESPACE LOG_DATA, 
  PARTITION PMAX VALUES LESS THAN (MAXVALUE)
    TABLESPACE LOG_DATA
)
AS
SELECT * FROM DBA_OBJECTS ;

 

2. 建立一個PK, 同時生成global index: 
alter table DFMS.TEST04 add constraint pk_id primary key(object_id); 

建立一個local index :
CREATE INDEX DFMS.IDX1_TEST04 ON DFMS.TEST04
(OBJECT_NAME)  LOCAL ;

 

3. 我們透過dba_indexes檢視檢視global index的狀態發現是valid : 
select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ;

本地索引local index透過dba_indexes檢視的狀態是N/A, 需要透過
dba_ind_partitions來檢視,可以看到每個索引分割槽都是USABLE狀態。
而透過DBA_PART_INDEXES可以看到這個本地分割槽索引的整體狀態。

select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
select * from dba_indexes where index_name='IDX1_TEST04' ;
select * from DBA_PART_INDEXES where index_name='IDX1_TEST04' ;

 

4. 因為存在maxvalue,我們先測試split對全域性及本地索引的影響 .

4.1 新分割槽中都有資料的情況

alter table test04 split partition pmax at (80000) into 
(partition p5 tablespace log_data ,
 partition pmax  tablespace log_data); 

我們從table的指令碼可以看出pmax被分成p5和pmax兩部分 :

....
  PARTITION P5 VALUES LESS THAN (80000), 
  PARTITION PMAX VALUES LESS THAN (MAXVALUE) ;
.....


顯然由於select max(object_id) from TEST04 的行數是101769,split
後舊分割槽中符合less than 80000的留在了第一個分割槽p5,其他的都存在
了第二個分割槽(新的pmax分割槽)。

我們查詢global index及local index的狀態:

select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ; 

這裡顯然觸發了資料的移動,global index索引狀態變成UNUSABLE.

select * from dba_ind_partitions where index_name='IDX1_TEST04' ;

因為新split出來的分割槽(這裡指p5)中有資料,原pmax中的資料被拆分到
新分割槽p5及新的pmax中,發現p1,p2,p3,p4 對應的本地索引仍然是USABLE,
而新的p5及新pmax對應的本地索引都是UNUSABLE. 

OK, 我們對global index及p5,pmax對應的本地分割槽索引進行rebuild :

alter index PK_ID rebuild online; 

然後查詢發現global index變成valid :
select index_name, status, last_analyzed,partitioned
from dba_indexes where index_name='PK_ID' ; 

alter index IDX1_TEST04 rebuild partition p5 online; 
alter index IDX1_TEST04 rebuild partition pmax online;  
執行之後查詢:
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
可以看到兩個索引分割槽p5及pmax狀態都變成USABLE .

 

4.2 新分割槽中有一個沒有資料

TEST04 的行數是101769,那麼我們將p6新分割槽設定為110000,那麼pmax分割槽
顯然就沒有資料了。

alter table test04 split partition pmax at (110000) into 
(partition p6 tablespace log_data , partition pmax  tablespace log_data); 

檢視global index及local index可以看到全域性索引及每個本地索引分割槽都是
USABLE, 這是因為沒有觸發資料移動 。
select index_name, status, last_analyzed,partitioned from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;

備註:在split pmax分割槽時新的分割槽名稱可以隨便起(不一定含有pmax),比如上面
的可以使用p6,p7, 只是p6會遵循less than 110000, 而第二個分割槽p7仍然是less
than maxvalue. 

 還有因為這裡是表空間沒有變化,如果非空的分割槽儲存屬性和原來的儲存屬性不一樣,也會發生資料移動,也會導致索引失效。

 

5. 測試drop partition對全域性及本地索引的影響。

對test04表的最後一個沒有資料的pmax分割槽進行刪除動作。
alter table test04 drop partition pmax ; 

因為刪除的分割槽沒有資料,所以不涉及資料變化,所以對全域性及本地所以
都沒有影響 。

假設我們要刪除有資料的部分,既不保留分割槽也不保留資料,那麼本地索引
不會受到影響,global index會失效。
alter table test04 drop partition p6 ; 
查詢
select * from dba_indexes where index_name='PK_ID' ;
全域性索引失效,狀態變成UNUSABLE .
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
本地索引(其他分割槽)狀態不變,為USABLE .

 


6. 測試add partition 對全域性索引和本地索引的影響。
alter table test04 add partition p6  values less than (120000) ;
查詢狀態:
select * from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
發現Range分割槽,加入分割槽對於全域性及本地索引都沒有影響。
同樣測試list分割槽,也可以知道加入分割槽對於全域性及本地索引都沒有影響。
這主要是因為沒有觸發資料的移動。

對於Hash分割槽,由於add parittion會發生資料分佈平衡的I/O操作,資料
會發生移動,所以本地分割槽索引及全域性索引都會置為UNUSABLE, 需rebuild.
下面做簡單測試:


CREATE TABLE DFMS.TEST05
PARTITION BY HASH (OBJECT_ID)
PARTITIONS 8
STORE IN (LOG_DATA)
AS SELECT * FROM DBA_OBJECTS ; 

加入global及local index .
alter table DFMS.TEST05 add constraint pk_test05_id primary key(object_id); 
CREATE INDEX DFMS.IDX1_TEST05 ON DFMS.TEST05 (OBJECT_NAME)  LOCAL ;

加入新分割槽:
alter table test05 add partition ; 

查詢
select * from dba_indexes where index_name='PK_TEST05_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST05' ;
發現global index是UNUSABLE狀態,本地分割槽索引中的第一個和最後一個
分割槽的本地分割槽索引是UNUSABLE狀態,其它是USABLE. 顯然因為資料從第一
個分割槽被拆分到了新的hasn分割槽,所以這兩個分割槽中的資料發生了移動,
導致了本地分割槽索引的失效,因為有資料行的移動,當然global index也
變成了失效狀態(UNUSABLE) .  

 

 


7. 測試truncate partition 對全域性索引和本地索引的影響。


Truncate partition 就像truncate table 一樣,直接從頭部截斷資料。在不指
定update indexes 子句的情況下,truncate partition 也會造成分割槽所在表的
global 索引失效。語法非常簡單:
alter table tbname truncate partition/subpartition ptname;

alter table test04 truncate partition p6 ;
查詢
select * from dba_indexes where index_name='PK_ID' ;
select * from dba_ind_partitions where index_name='IDX1_TEST04' ;
發現global index索引失效,本地分割槽索引狀態都是USABLE .

 

8. 其他操作如 Merge Partitions,Exchange Partitions及coalesce partitions
等較少使用,這裡不做測試。 

 

其實總之,如果發生資料移動,那麼索引肯定是需要注意的。

 

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

相關文章