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')));

表已创建。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-628017/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-628017/