When to Use Range or Interval Partitioning

Range partitioning is a convenient method for partitioning historical data. The
boundaries of range partitions define the ordering of the partitions in the tables or
indexes.


Interval partitioning is an extension to range partitioning in which, beyond a point in
time, partitions are defined by an interval. Interval partitions are automatically created
by the database when data is inserted into the partition.
间隔分区是rang分区的一个扩展，超出的时间点会自动按照间隔创建一个分区。


Example 3–3 creates the table salestable for a period of two years, 2005 and 2006, and partitions it by range according to the column s_salesdate to separate the data into eight quarters, each corresponding to a partition. Future partitions are created automatically through the monthly interval definition. Interval partitions are created in the provided list of tablespaces in a round-robin manner. Analysis of sales figures by a short interval can take advantage of partition pruning. The sales table also supports a rolling window approach.


Example 3–3 Creating a table with range and interval partitioning

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
 PARTITION sal06q3 VALUES LESS THAN (TO_DATE('01-OCT-2006', 'DD-MON-YYYY'))
 TABLESPACE tbs3,
 PARTITION sal06q4 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY'))
 TABLESPACE tbs4);