Interval Range Partitioning v13

Interval Range Partitioning is an extension to range partitioning that allows a database to automatically create a new partition when the newly inserted data exceeds the range of an existing partitioning. To implement interval range partitioning, include the INTERVAL clause and specify the range size for a new partition.

The high value of a range partition (also known as the transition point) is determined by the range partitioning key value. The database creates new partitions for inserted data with values that are beyond that high value.

If an interval is set to 1 month and if data is inserted for two months after the current transition point, only the partition for that month is created and not the intervening partition. For example, you can create an interval range partitioned table with a monthly interval and a current transition point of February 15, 2019. Now, if you try to insert data for May 10, 2019, then the required partition for April 15 to May 15, 2019 is created and data will be inserted into that partition. The partition for February 15, 2019 to March 15, 2019 and March 15, 2019 to April 15, 2019 is skipped.

For information about Interval Range Partitioning syntax, see CREATE TABLE...PARTITION BY.

Restrictions on Interval Range Partitioning

The following restrictions apply to the INTERVAL clause:

• Interval range partitioning is restricted to a single partition key; that key must be a numerical or date range.
• You must define at least one range partition.
• The INTERVAL clause is not supported for index-organized tables.
• A domain index cannot be created on an interval range partitioned table.
• In composite partitioning, the interval range partitioning can be useful as a primary partitioning mechanism but not supported at the subpartition level.
• DEFAULT and MAXVALUE cannot be defined for an interval range partitioned table.
• NULL, Not-a-Number, and Infinity values cannot be specified in the partitioning key column.
• Interval range partitioning expression must yield constant value and must not be a negative value.
• The partitions for an interval range partitioned table are created in increasing order only.