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

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

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

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

1、分區表索引的分類

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

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

2、針對分區表做各種DDL操作，查看對索引的影響

createtable tmp_test_range

(

  idnumber,

  id_local number,

  namevarchar2(30),

  int_date date,

  bz varchar2(20)

)

PARTITIONBYRANGE(int_date)

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

(

partition P201511 valueslessthan(to_date('20151201','yyyymmdd')),

partition P20151201 valueslessthan(to_date('20151202','yyyymmdd')),

partition P20151203 valueslessthan(to_date('20151203','yyyymmdd')),

partition P20151204 valueslessthan(to_date('20151204','yyyymmdd')),

partition P20151205 valueslessthan(to_date('20151205','yyyymmdd')),

partition P20151206 valueslessthan(to_date('20151206','yyyymmdd')),

partition P20151207 valueslessthan(to_date('20151207','yyyymmdd'))

);

向分區表中插入數據：

declare

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

begin

  for c in1 .. 9loop

    for d in1 .. 100loop

      insertinto tmp_test_range values(c || d,c||d,'測試數據', v_date,'BZ');

    endloop;

    v_date := v_date +1;

  endloop;

  commit;

end;

創建索引：

分別在ID,ID_LOCAL 和創建一個全局索引和分區索引

createindex I_TMP_TEST_RANGE_G on tmp_test_range(id)nologging;

createindex I_TMP_TEST_RANGE_L on tmp_test_range(id_local)nologgingLOCAL;
查看索引的狀態：

local索引：

Global索引：

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

添加分區和對其中一個分區重命名：

ALTERTABLE tmp_test_range ADDPARTITION  P20151208 valueslessthan(to_date('20151208','yyyymmdd'));

ALTERTABLE tmp_test_range RENAMEPARTITION P20151207 TO P20151207_2;

查看索引情況：

Global

Local 索引：

總結：添加分區和對分區重新命名并不會導致Global和local索引失效。

刪除表中的分區：

ALTERTABLE tmp_test_range DROPPARTITION  P20151208 ;

清空其中一個分區中的數據：

ALTERTABLE tmp_test_range TRUNCATEPARTITION P201511;

查看索引情況：

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

Global 索引：

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

查看某個分區的數據（P201511），看下圖是存在數據的：

現在對分區進行刪除：

altertable tmp_test_range droppartition P201511;

或者：
ALTERTABLE tmp_test_range TRUNCATEPARTITION P201518;

查看Global索引：

唉，還是失效了，所以在刪除分區表中的分區的時候，一定要確認有沒有數據存在。

總結：

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

合并分區：

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

ALTERTABLE tmp_test_range MERGEPARTITIONS P201511,P20151201 INTOPARTITION P20151208;

查看索引的情況：

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

local 索引不會維護合并后的分區，但是不會影響其他的分區，合并分區操作還會將原來被合并的分區刪除。

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

ALTERTABLE tmp_test_range MERGEPARTITIONS P201511,P20151201 INTOPARTITION P20151208 update  indexes ;

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

拆分分區：

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

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

ALTERTABLE tmp_test_range SPLITPARTITION P201511 at(date'2015-11-28')    INTO(PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE)updateindexes;

查看索引的情況：

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

ALTERTABLE tmp_test_range SPLITPARTITION P201511 at(date'2015-11-28')

    INTO(PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);

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

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

總結：

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

交換分區：

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

ALTERTABLE tmp_test_range EXCHANGEPARTITION P201511 WITHTABLE tmp_test_range_2;

ALTERTABLE tmp_test_range EXCHANGEPARTITION P201511  WITHTABLE tmp_test_range_2 updateINDEXES;

查看兩個索引情況：

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

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

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

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:馮棟華