關於interval partitioning

今天一個開發的同事問我一個問題，說在系統中目前需要一個臨時的解決方案，定義了一張表，目前想設定為分割槽表，因為會和外圍系統互動產生大量的資料，所以需要按照小時來做分割槽，按照目前的資料情況，可能每個小時的資料量都在幾十萬~百萬.
如果按照常規思路來說，那這個需求基本上時候不可能實現的.
一個是分割槽的限制數，按照小時來分割槽，那麼1天（24小時)*365(天數）=8760個分割槽，一年需要產生這麼多的分割槽，支援這麼多的分割槽，效能肯定會受到影響，不排除bug的可能。
第二個是如果在業務閒時，如果事先建立了分割槽，但是沒有資料就顯得有些浪費了，按照目前的系統互動情況，還不能完全肯定精確的時間點來限定什麼時候產生資料，什麼時候不產生資料。
第三個是需要定期的去清理分割槽的資料，對於限定時間內的分割槽資料進行清理。如果分割槽規則不合理，就可能會影響到其它的資料。比如按照小時來分割槽，設定24個分割槽，那麼做資料清理的時候就很麻煩，如果刪錯了資料是很嚴重的。

基於以上三點，可以考慮使用interval partitioning來實現，這個特性是在11g之後推出的，是對range partition的擴充套件。也是自動化分割槽的一個大膽嘗試。
我使用瞭如下的例子來簡單說明。
--建立序列，來遞增作為主鍵id.
CREATE SEQUENCE TEST_ID_SEQ START WITH 1 INCREMENT BY 1 CACHE 1000 NOCYCLE MAXVALUE 999999999999999;
 
--建立表,建立了分割槽表。亮點就是標黃的部分，一下子少了很多的內容。為了測試快速簡單，我使用分鐘來作為間隔自動生成分割槽。
CREATE TABLE TEST_NEW_PARTITION
(
  TEST_SEQ_ID            NUMBER(16),
  SYS_CREATION_DATE     DATE,
  MEMO  VARCHAR2(100)
)
PARTITION BY RANGE (SYS_CREATION_DATE) INTERVAL(NUMTODSINTERVAL (1, 'MINUTE'))
(
  PARTITION START_PART VALUES LESS THAN (TO_DATE('2014-11-01', 'YYYY-MM-DD')) NOLOGGING 
)  ;

 --檢視分割槽的情況。 
SQL> COL PARTITION_NAME FORMAT A30
SQL> SET LONG 9999
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION'
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART                     TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

嘗試插入兩條記錄來看看，中間需要有一些時間間隔。
SQL> insert into  TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
1 row created.
間隔一會
SQL> insert into  TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
1 row created.
檢視分割槽情況，可以看到產生了兩個新的分割槽，分割槽命名是按照系統自動生成的，high_value中可以看到相應的分割槽間隔點。
在13:05~13:29之間的那20多分鐘時間內，因為沒有資料，這裡就沒有生成分割槽。
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART                     TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26223                     TO_DATE(' 2014-11-25 13:05:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26224                     TO_DATE(' 2014-11-25 13:29:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

然後我們再嘗試插入一條記錄。
SQL> insert into  TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'b');
1 row created.

分割槽情況如下。
SQL> select partition_name,high_value from user_tab_partitions where table_name='TEST_NEW_PARTITION';
PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
START_PART                     TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26223                     TO_DATE(' 2014-11-25 13:05:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26224                     TO_DATE(' 2014-11-25 13:29:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P26225                     TO_DATE(' 2014-11-25 13:30:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
#1 關於間隔分割槽的支援，目前可以採用如下的兩種形式（日期和數字)

numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})  使用日期形式來間隔分割槽

Interval (number) 按照數字來間隔分割槽

#2 關閉/開啟自動化分割槽的功能
關閉功能其實很簡單，就是把interval的部分設為空。
SQL> alter table test_new_partition set interval();
Table altered.
然後再嘗試插入一條記錄。就會發現被reject了。
SQL> insert into  TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a');
insert into  TEST_NEW_PARTITION values(TEST_ID_SEQ.nextval,sysdate,'a')
                                       *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
如果需要開啟也是類似，設定interval的值就可以了。比如下面的命令。
SQL> ALTER TABLE TEST_NEW_PARTITION SET INTERVAL(NUMTODSINTERVAL (1, 'MINUTE'));
Table altered.

#3 關於過期分割槽的清理
可以使用如下的簡單pl/sql來進行簡單的清理。目前設定的間隔是分鐘，我們假定刪除100分鐘以前的分割槽資料。

set serveroutput on buffer 1000000
declare
    v_date        date;
begin
  for rec in  (select a.table_name, a.partition_name, high_value, 100 as retention from user_tab_partitions  a where table_name='TEST_NEW_PARTITION' and partition_name<>'START_PART' order by 1,2)
        loop
        begin
           execute immediate 'select ' || rec.high_value || ' from dual' into v_date;
           if (v_date < (sysdate - rec.retention/24/60 ))
           then
               dbms_output.put_line ('Dropping partition '||rec .table_name||'.'||rec.partition_name||' - '||to_char(v_date,'YYYY-MM-DD'));
               execute immediate 'alter table ' || rec.table_name ||  ' drop partition ' || rec.partition_name;
               dbms_output.put_line ('Dropping partition '||rec.table_name||'.'||rec.partition_name||' - Dropped');
           end if ;
        end;
  end loop;
end;
/
執行指令碼後的結果如下：
Dropping partition TEST_NEW_PARTITION.SYS_P26223 - 2014-11-25
Dropping partition TEST_NEW_PARTITION.SYS_P26223 - Dropped
PL/SQL procedure successfully completed.

#4 storage的補充。
比如我們希望把分割槽的資料按照分割槽(POOL_DATA,POOL_IX,TOOLS)的形式進行儲存，就可以使用set store來設定。
SQL> alter table TEST_NEW_PARTITION set store in (POOL_DATA,POOL_IX,TOOLS);
Table altered.

這樣新增的分割槽就會分割槽按照順序迴圈在上面的3個表空間中分佈。

#5 分割槽的重新命名
目前還沒有發現這個特效能夠指定分割槽命名。如果需要按照要求進行修改，就可以使用下面的形式來修改。

alter table xxx rename partition xxxx  to xxxx

總之這個新特性顯得分割槽很動態，確實能省事不少，不過對於核心系統來說使用還是需要謹慎，畢竟我們需要管理資料，讓資料在控制之內，如果核心表出現問題還是很要命的。對於一些優先順序不高的模組可以嘗試一下，從目前的情況來看效果還是不錯的。