# Oracle Partition - Range Interval Partitioning

A range partitioning where the database automatically creates partitions for a specified interval.

## Prerequisites

• At least one range partition using the PARTITION clause.
• The partitioning key can only be a single column name from the table
• The single partitioning key column must be of NUMBER or DATE type.
• The single partitioning key column can not contains NULL (you can specify it either)
• The table is not an index-organized tables.
• The partitioning keys must be in the primary key if the primary key is local.

A virtual column can be used to partition on a transformed column value

The max number of partition is 1 Million, and for an integer column partition key:

$$\frac{Max(integerColumn)}{Interval} < 1 M$$

## Syntax

See Reference

where:

• partition: partition name. Example: “20110701”

## Concept

### Transition point

The high value of the range partitions is the transition point. The database automatically creates interval partitions for data beyond that transition point.

Example: the transition point is 1-1-2010 because 1-1-2010 is the max date.

PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

• The high bound of partition p3 represents the transition point. p3
• All partitions below the transition point (p0, p1, and p2 in this example) are in the range section
• All partitions above the transition point fall into the interval section.

### Interval

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table.

Example: the interval is one month (NUMTOYMINTERVAL(1, 'MONTH'))

PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );


See function:

### Boundary

The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

Example:

  PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

• the lower boundary for the January 2010 interval is January 1, 2010.
• the lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created.

Out of bound example: using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

### Storage

The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

## Management

### Change the Interval

to set a new interval for the table

ALTER TABLE t_part SET INTERVAL(NUMTODSINTERVAL(1,'DAY'));


### Disable / turn off interval partitioning

You can disable interval partitioning with the following statement:

ALTER TABLE <table name> SET INTERVAL ();


## Example

An interval partitioned table with monthly intervals where above the transition point of January 1, 2010, partitions are created with a width of one month.

CREATE TABLE interval_sales
( prod_id        NUMBER(6)
, cust_id        NUMBER
, time_id        DATE
, channel_id     CHAR(1)
, promo_id       NUMBER(6)
, quantity_sold  NUMBER(3)
, amount_sold    NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );