oracle分割槽表的常規操作導致對索引的影響

     oracle分割槽表目前已經很普遍的應用於我們的生產系統，但是在日常需要維護分割槽表的時候，一些對於分割槽表的基本操作的時候，我們難免會對分割槽表上的索引是否失效有些擔心，那麼今天我就帶大家看下具體哪些操作會導致分割槽表上的索引失效。

為了控制篇幅，本次實驗只針對RANGE分割槽，其他兩種分割槽請有興趣的同學自行測試哦~~~

1、分割槽表索引的分類

   熟悉分割槽表的人都應該知道，oracle分割槽表的索引型別分為兩種，一種是Local索引又稱本地索引，一種是Global索引也叫全域性索引。本次實驗不具體介紹這兩種索引對分割槽表訪問所產生的效能問題方面的差異，只介紹一些常用的DDL操作對分割槽表上的索引的一些影響。

如果想要知道兩種索引的效能差異，請期待下期的文章分享哦~~~~

2、針對分割槽表做各種DDL操作，檢視對索引的影響

2.1建立分割槽表，並建立全域性索引和分割槽索引

create table tmp_test_range

(

  id number,

  id_local number,

  name varchar2(30),

  int_date date,

  bz varchar2(20)

)

PARTITION BY RANGE(int_date)

--interval(numtodsinterval (1,'DAY'))

(

partition P201511 values less than (to_date('20151201','yyyymmdd')),

partition P20151201 values less than (to_date('20151202','yyyymmdd')),

partition P20151203 values less than (to_date('20151203','yyyymmdd')),

partition P20151204 values less than (to_date('20151204','yyyymmdd')),

partition P20151205 values less than (to_date('20151205','yyyymmdd')),

partition P20151206 values less than (to_date('20151206','yyyymmdd')),

partition P20151207 values less than (to_date('20151207','yyyymmdd'))

);

向分割槽表中插入資料：

declare

  v_date date := to_date('20151127', 'yyyy-mm-dd');

begin

  for c in 1 .. 9 loop

    for d in 1 .. 100 loop

      insert into tmp_test_range values (c || d,c||d, '測試資料', v_date, 'BZ');

    end loop;

    v_date := v_date + 1;

  end loop;

  commit;

end;

建立索引：

分別在ID, ID_LOCAL 和建立一個全域性索引和分割槽索引

create index I_TMP_TEST_RANGE_G on tmp_test_range(id) nologging;

create index I_TMP_TEST_RANGE_L on tmp_test_range(id_local) nologging LOCAL;
檢視索引的狀態：

local索引 ：

Global索引：

2.2  DDL操作對全域性索引的影響：

好了，前面的基礎工作已經準備完畢，下面我們開始做一些DDL操作，看下對全域性索引的影響。

新增分割槽和對其中一個分割槽重新命名：

ALTER TABLE tmp_test_range ADD PARTITION  P20151208 values less than (to_date('20151208','yyyymmdd'));

 

ALTER TABLE tmp_test_range RENAME PARTITION P20151207 TO P20151207_2;

檢視索引情況：

Global

 

Local 索引：

總結：新增分割槽和對分割槽重新命名並不會導致Global和local索引失效。

刪除表中的分割槽：

ALTER TABLE tmp_test_range DROP PARTITION  P20151208 ;

清空其中一個分割槽中的資料：

ALTER TABLE tmp_test_range TRUNCATE PARTITION P201511;

檢視索引情況：

local索引會將被刪除的分割槽上的local索引刪除，不會影響到其他分割槽的索引。

Global 索引：

OK，經過上面的實驗可能你很容易就能得出結論說，刪除分割槽不會導致Global索引失效，其實不然，讓我們看一種其他情況：

檢視某個分割槽的資料（P201511），看下圖是存在資料的：

現在對分割槽進行刪除：

alter table tmp_test_range drop partition P201511;

或者：
ALTER TABLE tmp_test_range TRUNCATE PARTITION P201518;

檢視Global索引：

唉，還是失效了，所以在刪除分割槽表中的分割槽的時候，一定要確認有沒有資料存在。

總結：

刪除分割槽表中的分割槽的時候 或者truncate 分割槽中的資料時，一定要確認分割槽中是否有資料存在，如果沒有資料不會導致Global失效，反之則會導致Global索引失效。而對其他分割槽上的local索引都不會造成影響。

合併分割槽：

合併分割槽有兩種方式，一種是維護索引的，一種是不維護索引。我們先來看不維護索引的。

ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,P20151201 INTO PARTITION P20151208;

檢視索引的情況：

總結：Global索引，做合併分割槽操作的時候會導致Global索引失效，所以操作的時候一定要當心哦！！！

local 索引不會維護合併後的分割槽，但是不會影響其他的分割槽，合併分割槽操作還會將原來被合併的分割槽刪除。

當然oracle 也提供了合併分割槽的時候維護索引的操作，當大家線上上操作的時候，請使用下面的語句對分割槽做合併。

ALTER TABLE tmp_test_range MERGE PARTITIONS P201511,P20151201 INTO PARTITION P20151208 update  indexes ;

這樣就不會在做合併分割槽操作的時候，導致索引失效了。

拆分分割槽：

拆分分割槽同樣也是有兩種方式，一種是直接拆分並不維護索引，另一種是帶維護索引的拆分方式。具體操作見下面的實驗：

我們先看維護索引的方式拆分：

ALTER TABLE tmp_test_range SPLIT PARTITION P201511 at (date'2015-11-28')    INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE) update indexes;

檢視索引的情況：

果然使用update indexes的方式進行拆分Global索引和local索引都是正常的。那下面我們再來看下不維護索引的方式：

ALTER TABLE tmp_test_range SPLIT PARTITION P201511 at (date'2015-11-28')

    INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE) ;

還是沒有令我們失望，使用這種直接拆分的方式兩種索引都有失效的情況發生，

local索引在新增的兩個分割槽上的索引失效，Global索引還是不負眾望的失效了。

總結：

對分割槽表進行拆分分割槽的時候如果不加引數 update indexes 會導致 新增分割槽上的local索引失效，Global索引失效。採用update  indexes 的方式這兩種索引都不會失效。

交換分割槽：

交換分割槽同樣也是有兩種方式，一種是自動維護索引的，一種是直接交換分割槽不做維護索引操作，顯然第二種方式會導致索引失效。具體見下面的實驗（下面兩個不要同時操作）：

ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511 WITH TABLE tmp_test_range_2;

 

ALTER TABLE tmp_test_range EXCHANGE PARTITION P201511  WITH TABLE tmp_test_range_2 update INDEXES;

 

檢視兩個索引情況：

果不其然，交換分割槽還是會導致Global索引失效，local索引還是被交換的分割槽上的索引失效。所以在做交換分割槽的時候，我們還是可以使用update indexes 來維護索引Global索引，但是對於local索引即使使用update  indexes 也會導致local索引失效，還是要再重新維護 。

3、總結：

好了，上面我們說了那麼多，讓我們總結一下，具體哪些操作會對分割槽表上的索引有一定的影響。

 兩種索引都不會產生影響的操作：

1.新增分割槽

2.刪除分割槽（分割槽中沒有資料）

3.對分割槽重新命名

會產生影響的：

1.合併分割槽

新增分割槽上的local索引失效

Global索引失效

PS：使用update indexes 的方式可以避免索引失效的發生(包括local索引和Global索引)。

2、拆分分割槽

拆分出來的分割槽上的local索引失效

Global索引失效

PS：使用update indexes 的方式可以避免索引失效的發生(包括local索引和Global索引)。

3、分割槽交換

被交換分割槽上的local索引失效

Global索引失效

PS：使用update indexes 的方式可以避免Global索引失效的發生，不能避免local索引失效。

4、刪除分割槽 或者truncate分割槽中的資料 時，如果被刪除的分割槽上有資料存在，會導致Global索引失效。

 

透過上面的實驗我們可以看出對於分割槽表來說，且不論效能如何，就單單對方便資料管理來說，還是建立local索引更加方便對分割槽表中資料的管理。

author:馮棟華