分區表中全局及本地分區索引什么時候會失效及如何處理final

這篇文章給大家介紹分區表中全局及本地分區索引什么時候會失效及如何處理final，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

分區表中 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
等較少使用，這里不做測試。 

其實總之，如果發生數據移動，那么索引肯定是需要注意的。

關于分區表中全局及本地分區索引什么時候會失效及如何處理final就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。