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> 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')));
表已创建。


复制