巧妙使用exchange partition的一個案例

前幾天寫過一篇文章討論過分割槽表的線上重定義，其實就是另外一個分割槽表和現有的分割槽表做資料字典資訊的交換
http://blog.itpub.net/23718752/viewspace-1734195/
當然了除此之外還是需要一些相應的許可權，在這個過程中會在內部做類似物化檢視一樣的資料重新整理，保持資料表的可訪問性。
在不同的場景中還是會有不同的取捨，比如現在的場景，情況發生了一些變化，分割槽存在問題，要重新分割槽是肯定的，除此之外，開發希望把一些舊資料做一些清理，比如根據時間來分割槽，可能對於開發來說，最近一段時間的資料保留就可以了，之前很舊的資料就只是需要做一個備份，直接清掉，基本評估下來，可能90%以上的資料都需要做清理。
所以這個時候還是使用線上重定義就有一些弊端了。
首先是時間的問題，線上重定義的過程中，其實內部還是在做資料的複製工作。相當於把資料從一個源流動到另外一個源。內部是這樣的資料重新整理，對外保持始終可以訪問，所以對於上億條記錄來說這個時間就比較長了。
其次就是效率問題，因為重新分割槽後，可能大部分資料都不需要了，這個時候做線上重定義還會做資料複製，然後在一定的時間之後還是需要再次做資料清理。這個時候相當於做了重複工作。效率會大大折扣。

這個時候根據目前的問題情況，考慮透過以下的方式來實現。
首先是資料內部的複製，為了減少這種開銷，可以考慮把分割槽表的預設分割槽和一個普通表做交換，這個過程是一個資料字典級的變更，所以速度還是很快的。
然後可以交換後的分割槽做split partition的操作。這個時候split操作時間會持續極短。影響相對來說很小。
然後就是資料的清理，這個時候清理工作就會變為一種選擇性的資料匯入，因為需要匯入的資料量還是很小的。所以只需要把需要時間範圍內的資料匯入即可。
我們來簡單試一試。
還是之前所用的分割槽表，現在只有一個預設分割槽。
CREATE TABLE tab_part 
   ( 
   col1 varchar2(30), 
   col2 DATE 
   ) 
   partition BY range(col2) 
   (  
     partition tab_part_maxvalue values less than (maxvalue)
   ); 
我們希望達到的效果是下面的分割槽形式。
CREATE TABLE tab_part 
   ( 
   col1 varchar2(30), 
   col2 DATE 
   ) 
   partition BY range(col2) 
   ( 
   partition tab_part_2014 VALUES less than (to_date('2014-08-01','yyyy-mm-dd')), 
   partition tab_part_2015 VALUES less than (to_date('2015-08-01','yyyy-mm-dd')),  
   partition tab_part_maxvalue values less than (maxvalue)
   );
然後給分割槽表建立分割槽索引，然後我們簡單插入幾條資料。
create index inx_tab_part on tab_part(col1) local;

INSERT INTO tab_part  VALUES(1,SYSDATE-400);                                                                      
INSERT INTO tab_part  VALUES(11,SYSDATE-600);
commit;
資料情況如下：
COL1                           COL2
------------------------------ ---------
1                              09-APR-15
11                             05-MAR-14
這個時候我們建立一個表test_tab01來做為中間過渡的一個表。

CREATE TABLE TEST_TAB01(
col1 varchar2(30),                
col2 DATE);

這個時候就開始使用exchange partition來把分割槽的資料做個交換。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue
  WITH TABLE TEST_TAB01 ;
交換的速度很快，來看看操作之後的資料情況，可以看到資料都到了test_tab01裡面。

SQL> select count(*)from tab_part;

  COUNT(*)
----------
         0

SQL> select count(*)from test_tab01;

  COUNT(*)
----------
         2
這個時候分割槽還是沒變，但是資料給交換出來了。
SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TAB_PART                       TAB_PART_MAXVALUE
我們接下來要做split partition的工作。

SQL> alter table tab_part  SPLIT PARTITION tab_part_maxvalue at (to_date('2014-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2014 , PARTITION tab_part_maxvalue);

Table altered.

SQL> alter table tab_part  SPLIT PARTITION tab_part_maxvalue at (to_date('2015-08-01','yyyy-mm-dd')) INTO ( PARTITION tab_part_2015 , PARTITION tab_part_maxvalue);

Table altered.
這個時候再來看分割槽的情況，就達到了我們預期的要求。

SQL> select table_name,partition_name from user_tab_partitions where table_name='TAB_PART';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TAB_PART                       TAB_PART_2014
TAB_PART                       TAB_PART_2015
TAB_PART                       TAB_PART_MAXVALUE
如果對exchange partition還是有一定的疑慮，想索引會不會受到影響，如果是本地索引是沒有問題的，如果是global全域性索引，就需要rebuild

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_TAB_PART';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
INX_TAB_PART                   TAB_PART_2014                  USABLE
INX_TAB_PART                   TAB_PART_2015                  USABLE
INX_TAB_PART                   TAB_PART_MAXVALUE              USABLE

這個時候重新分割槽完成了，要做的事情就是匯入資料了，選擇性的匯入資料，根據時間戳即可。
當然了好幾億條記錄，不建個索引根本說不過去。
create index inx_test_tab01 on test_tab01(col);
我們就可以使用insert的方式匯入資料即可。

insert into tab_part select *from test_tab01 where col>sysdate-10;

最後為了把exchange partiton的一個精髓突出出來和線上重定義區分開了，可以做一個很簡單的小例子。
我們還是建立原來的表tab_part

CREATE TABLE tab_part 
   ( 
   col1 varchar2(30), 
   col2 DATE 
   ) 
   partition BY range(col2) 
   ( 
   partition tab_part_maxvalue values less than (maxvalue)
   ); 

create index inx_tab_part on tab_part(col1) local;
我們接著建立一個表test_tab01，這個時候唯一的不同之處就是欄位名不同，但是資料型別相同。

CREATE TABLE TEST_TAB01(
a varchar2(30),                
b DATE);
然後嘗試去做exchange partition的時候是沒有問題的。

ALTER TABLE tab_part EXCHANGE PARTITION tab_part_maxvalue
  WITH TABLE TEST_TAB01 ;
我們看看交換分割槽之後的表結構變化情況

SQL> desc tab_part
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(30)
 COL2                                               DATE

SQL> desc test_tab01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(30)
 B                                                  DATE

 可以看到欄位名沒有任何變化，還是原來的欄位名，但是在內部做了資料字典資訊的交換，把相應的段資訊做了交換而已。這也就exchange partition的主要思想。
透過這個案例可以看到，exchange partition還是大有可為，而且在很多場景下可以達到很滿意的效果，線上重定義也不錯，不過在選擇的時候還是需要綜合評定，沒有最好的方法，最有最適合的方法。