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 beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

CREATE TABLE salestable

(s_productid NUMBER,

s_saledate DATE,

s_custid NUMBER,

s_totalprice NUMBER)

PARTITION BY RANGE(s_saledate)

INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (tbs1,tbs2,tbs3,tbs4)

(PARTITION sal05q1 VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY'))

TABLESPACE tbs1,

PARTITION sal05q2 VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY'))

TABLESPACE tbs2);

The following example shows how to change the sales table in the sample sh schema from range partitioning to start using monthly interval partitioning.

ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

將已有範圍分區表變為interval partition table，注意不能有MAXVALUE值

ALTER TABLE GC.CCS_T_EQ_EVENT_LOG

SET INTERVAL (NUMTODSINTERVAL(7,'DAY'));

ALTER TABLE SFISM4.R_WIP_TRACKING_T

SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));

指定新interval partition的表空間和storage

ALTER TABLE GC.CCS_T_EQ_EVENT_LOG

MODIFY DEFAULT ATTRIBUTES

TABLESPACE CSS_ASSMINDEX

STORAGE (INITIAL 50M

NEXT 50M);

You cannot use interval partitioning with reference partitioned tables.

Serializable transactions do not work with interval partitioning. Inserting data into a partition of an interval partitioned table that does not have a segment yet causes an error.

參考：http://space.itpub.net/17203031/viewspace-706173

ITPub博客

Interval Partition table 11G

最新文章