Oracle11g INTERVAL分割槽新增分割槽策略

INTERVAL分割槽是Oracle11g新增的特性，這種範圍分割槽不需要定義MAXVALUE，Oracle會根據分割槽定義的INTERVAL來動態的分配新分割槽來容納超過範圍的資料。

Oracle在分配新分割槽的時候只會給存在資料的分割槽進行分配，對於不存在資料的分割槽並不會馬上分配，而是在需要的時候才去分配。

對於這個分割槽分配策略，是在無意中發現的：

SQL> CREATE TABLE T_INTERVAL_PART
2 PARTITION BY RANGE (CREATED)
3 INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
4 (PARTITION P1 VALUES LESS THAN (TO_DATE('2007-9-1', 'YYYY-MM-DD')))
5 AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
2 WHERE TABLE_NAME = 'T_INTERVAL_PART'
3 ORDER BY 2;

TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------- ---------------------------------------------------------------------
T_INTERVAL_PART P1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GR
T_INTERVAL_PART SYS_P113 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND
T_INTERVAL_PART SYS_P114 TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND

上面這個例子就是在介紹INTERVAL分割槽時使用的例子，不過這個例子中存在一個比較奇怪的顯現，那就是對於2007年11月的分割槽對於的分割槽名稱中的序號反而小於2007年10月的。

看到這個現象，第一個反應就是認為，Oracle根據INTERVAL的值的上限，從高到低依次建立分割槽，但是隨後的測試發現並非如此：

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
2 WHERE TABLE_NAME = 'T_INTERVAL_PART'
3 ORDER BY 2;

TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------- ---------------------------------------------------------------------
T_INTERVAL_PART P1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GR
T_INTERVAL_PART SYS_P113 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND
T_INTERVAL_PART SYS_P114 TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND
T_INTERVAL_PART SYS_P115 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND

Oracle並沒有建立從07年12月到08年12月之間的所有的分割槽，而是僅僅建立了08年12月份的分割槽。這說明Oracle會根據插入資料的分割槽鍵值來分配所需的分割槽，中間沒有包含資料的分割槽不會被建立。

同時也解釋了為什麼07年11月分割槽的分割槽序號小於10月份的，這是由於在建立分割槽表並插入資料的過程中，首先出現了11月份的記錄，而後才出現了10月份的。

下面驗證一下這個結論：

SQL> SELECT * FROM (SELECT ROWNUM RN, OWNER, OBJECT_NAME, CREATED FROM DBA_OBJECTS)
2 WHERE CREATED >= TO_DATE('2007-10-1', 'YYYY-MM-DD')
3 AND ROWNUM = 1;

RN OWNER OBJECT_NAME CREATED
---------- ------------------------------ ------------------------------ -------------------
68234 YANGTK T_INTERVAL_PART 2007-10-21 02:16:06

SQL> SELECT * FROM (SELECT ROWNUM RN, OWNER, OBJECT_NAME, CREATED FROM DBA_OBJECTS)
2 WHERE CREATED >= TO_DATE('2007-9-1', 'YYYY-MM-DD')
3 AND CREATED < TO_DATE('2007-10-1', 'YYYY-MM-DD')
4 AND ROWNUM = 1;

RN OWNER OBJECT_NAME CREATED
---------- ------------------------------ ------------------------------ -------------------
68240 SYS T_PART 2007-09-15 16:25:15

由於查詢DBA_OBJECTS檢視的時候，先查詢到建立時間為10月21日的記錄，因此，INTERVAL分割槽表先建立了上限為11月1日的分割槽，隨後建立了上限為10月1日的分割槽。

SQL> ROLLBACK;

回退已完成。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
2 WHERE TABLE_NAME = 'T_INTERVAL_PART'
3 ORDER BY 2;

TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- -------------- ---------------------------------------------------------------------
T_INTERVAL_PART P1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GR
T_INTERVAL_PART SYS_P113 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND
T_INTERVAL_PART SYS_P114 TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND
T_INTERVAL_PART SYS_P115 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALEND

而且，即使觸發Oracle分配新的分割槽的資料被回滾了，新增分割槽也不會被刪除。從這一點可以推斷，INTERVAL分割槽的分割槽分配採用的是自治事務。