Oracle Interval-Partition 解决Range分区大难题

在实际运用Range分区时，遇到了这样的难题：

createtableTMP_LXQ_1

(

 PROPOSALNO             VARCHAR2(22),

 STARTDATE              DATE

)

partitionbyrange(STARTDATE) (

partitionpart_t01values less than(to_date('2008-01-01','yyyy-mm-dd')) ,

partitionpart_t02values less than(to_date('2009-01-01','yyyy-mm-dd')) ,

partitionpart_t03values less than(to_date('2010-01-01','yyyy-mm-dd')) ,

partitionpart_t04values less than(to_date('2011-01-01','yyyy-mm-dd')) ,

partitionpart_t05values less than(to_date('2012-01-01','yyyy-mm-dd')) ,

partitionpart_t06values less than(to_date('2013-01-01','yyyy-mm-dd')) ,

partitionpart_t07values less than(maxvalue)

);

本例中的时间只是到了2013年，然后2013年之后的时间完全放入到了maxvalue中，这确实也是一种解决办法，即有除去2008-2013年的数据时，数据库不会报错。但是这样就改变了我们分区的初衷，分区是想让各个部分的数据均衡，以加快查询。

在oracle11g出现之前，实际工作中经常会遇到这种情况，而处理这种情况，通常是由DBA或者开发人员手动进行分区或者直接定义maxvalue。

Oracle 11g的新增特性Interval分区可以解决这个问题，下面介绍一下Interval分区。

 

一、interval分区

INTERVAL分区是Oracle11g新增的特性，它是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。

示例：

createtable TMP_LXQ_1

(

proposalnovarchar2(22),

startdatedate

)

partitionby range(STARTDATE)

interval(numtoyminterval(1,'year'))(

partitionpart_t01 values less than(to_date('2008-01-01','yyyy-mm-dd')) ,

partitionpart_t02 values less than(to_date('2009-01-01','yyyy-mm-dd')) ,

partitionpart_t03 values less than(to_date('2010-01-01','yyyy-mm-dd')) ,

partitionpart_t04 values less than(to_date('2011-01-01','yyyy-mm-dd')) ,

partitionpart_t05 values less than(to_date('2012-01-01','yyyy-mm-dd')) ,

partitionpart_t06 values less than(to_date('2013-01-01','yyyy-mm-dd'))

);

 

如果插入2014年的值，系统会自动的添加一个分区，分区范围为2014-01-01到2014-12-31日。

 

二、interval分区和range分区的转换

ALTERTABLE TMP_LXQ_1 SET INTERVAL (numtoyminterval(1,'year'));

对于INTERVAL分区表，新增的超过分区上限的数据会自动导致对应的INTERVAL分区被建立。

同样INTERVAL分区表可以方便的转化为RANGE分区表，只需要不输入INTERVAL的值即可：

ALTER TABLETMP_LXQ_1 SET INTERVAL ();

 

三、interval分区的特点

1.由range分区派生而来

2.以定长宽度创建分区（比如年、月、具体的数字（比如100、500等））

3.分区字段必须是number或date类型

4.必须至少指定一个range分区（永久分区）

5.当有记录插入时，系统根据需要自动创建新的分区和本地索引

6.已有的范围分区可被转换成间隔分区（通过ALTER TABLE SET INTERVAL选项完成）

7.IntervalPartitioning不支持支持索引组织表

8.在Interval Partitioning表上不能创建domain index

 

四、interval分区问题

在oracle自动创建分区的时候，系统会默认指定一个分区名，系统默认创建的分区名字与我们的分区命名规范会有一定的差距，这个问题暂时还不知道有什么解决方法。

 

INTERVAL Clause
Use this clause to establish interval partitioning for the table. Interval partitions are
partitions based on a numeric range or datetime interval. They extend range
partitioning by instructing the database to create partitions of the specified range or
interval automatically when data inserted into the table exceeds all of the range
partitions.
■ For expr, specify a valid number or interval expression.
■ The optional STORE IN clause lets you specify one or more tablespaces into which
the database will store interval partition data.
■ You must also specify at least one range partition using the PARTITION clause of
range_partitions. The range partition key value determines the high value of
the range partitions, which is called the transition point, and the database creates
interval partitions for data beyond that transition point.
Restrictions on Interval Partitioning The INTERVAL clause is subject to the following
restrictions:
■ You can specify only one partitioning key column, and it must be of NUMBER or
DATE type.
■ This clause is not supported for index-organized tables.
■ You cannot create a domain index on an interval-partitioned table.
■ Interval partitioning is not supported at the subpartition level.
■ Serializable transactions do not work with interval partitioning. Trying to insert
data into a partition of an interval partitioned table that does not yet have a
segment causes an error.
■ In the VALUES clause:
– You cannot specify MAXVALUE (an infinite upper bound), because doing so
would defeat the purpose of the automatic addition of partitions as needed.
– You cannot specify NULL values for the partitioning key column.