DATE型別INTERVAL分割槽

11g新增的INTERVAL分割槽使得RANGE分割槽的新建分割槽工作大為簡化，這個工作將隨著資料的插入由Oracle自動完成。

 

 

顧名思義INTERVAL分割槽需要提供一個INTERVAL，而對於字元型別是不存在INTERVAL的，因此只有NUMBER型別和DATE型別支援INTERVAL分割槽。

其中NUMBER型別的INTERVAL分割槽很簡單，因此這裡僅描述相對複雜一點的DATE型別的INTERVAL分割槽。需要注意這裡的DATE是泛指，包括DATE、TIMESTANP、TIMESTAMP WITH ZONE和TIMESTAMP WITH LOCAL ZONE多個日期相關的資料型別。

對於INTERVAL值的限定，有兩種方法，一種是透過NUMTOYMINTERVAL或NUMTODSINTERAL函式：

SQL> CREATE TABLE T_PART_INTER_MONTH
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')));

表已建立。

SQL> CREATE TABLE T_PART_INTER_DAY
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD')));

表已建立。

另一種等價的方法是直接利用INTERVAL表示式：

SQL> DROP TABLE T_PART_INTER_MONTH PURGE;

表已刪除。

SQL> DROP TABLE T_PART_INTER_DAY PURGE;

表已刪除。

SQL> CREATE TABLE T_PART_INTER_MONTH
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (INTERVAL '1' MONTH)
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')));

表已建立。

SQL> CREATE TABLE T_PART_INTER_DAY
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (INTERVAL '1' DAY)
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD')));

表已建立。

無論使用哪種方法，其實都是INTERVAL的方式，這種方式和ADD_MONTHS函式不同，不會自動處理月末的問題，因此以月為單位的INTERVAL不能以超過28日的日期作為最後一個範圍分割槽的上限：

SQL> CREATE TABLE T_PART_TEST
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (INTERVAL '1' MONTH)
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-3-29', 'YYYY-MM-DD')));
CREATE TABLE T_PART_TEST
*
第 1 行出現錯誤:
ORA-14767: 無法使用現有上限指定此間隔

避免這種錯誤其實很簡單，最簡單的是利用28日作為分割槽上限：

SQL> CREATE TABLE T_PART_TEST
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (INTERVAL '1' MONTH)
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-3-28', 'YYYY-MM-DD')));

表已建立。

或者再建立一個最高分割槽，避免超過28日的日誌作為最後一個分割槽的上限出現：

SQL> DROP TABLE T_PART_TEST PURGE;

表已刪除。

SQL> CREATE TABLE T_PART_TEST
  2  (ID NUMBER,
  3  NAME VARCHAR2(30),
  4  CREATE_DATE DATE)
  5  PARTITION BY RANGE (CREATE_DATE)        
  6  INTERVAL (INTERVAL '1' MONTH)
  7  (PARTITION P1 VALUES LESS THAN (TO_DATE('2010-3-28', 'YYYY-MM-DD')),
  8  PARTITION P2 VALUES LESS THAN (TO_DATE('2010-4-1', 'YYYY-MM-DD')));

表已建立。