Oracle 11g 的 interval 分区

官方文档

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data with values that are beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

For example, if you create an interval partitioned table with monthly intervals and you set the transition point at January 1, 2007, then the lower boundary for the January 2007 interval is January 1, 2007. The lower boundary for the July 2007 interval is July 1, 2007, regardless of whether the June 2007 partition was created.

You can create single-level interval partitioned tables and the following composite partitioned tables:

• Interval-range

• Interval-hash

• Interval-list

• Only one partitioning key column can be specified, and it must be of NUMBER or DATE type.

• Interval partitioning is not supported for index-organized tables.

• NULL values cannot be stored in the partitioning key column when using interval partitioning.

Interval partitioning supports a subset of the capabilities of range partitioning. For information about restrictions when using interval partitioning, refer to Oracle Database SQL Language Reference.

事例

建立以month_id_date 字段的分区

--建表
CREATE TABLE INTERVAL_PARTITION_TEST(
        MONTH_ID VARCHAR2(20),
        MONTH_ID_DATE DATE
)
PARTITION BY RANGE( MONTH_ID_DATE)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
        --历史数据只到2018年
        PARTITION MONTH_ID_201801 VALUES LESS THAN (  TO_DATE('201802','YYYYMM') )
)
;


--插入测试数据
insert into interval_partition_test values('201712',to_date('201712','yyyymm'));
insert into interval_partition_test values('201812',to_date('201812','yyyymm'));
insert into interval_partition_test values('201912',to_date('201912','yyyymm'));
insert into interval_partition_test values('202101',to_date('202101','yyyymm'));
insert into interval_partition_test values('202102',to_date('202102','yyyymm'));

 

自动分区结果

--查询表里面的分区
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=upper('interval_partition_test');

 

执行计划

 

参考博文：https://www.cnblogs.com/hllnj2008/p/5177220.html