Interval range partitioning v16

Suggest edits

Interval range partitioning is an extension to range partitioning that allows a database to create a partition when the inserted data exceeds the range of an existing partition. 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 partitions for inserted data with values that are beyond that high value.

Interval range partitioning example

Suppose an interval is set to one month. If data is inserted for two months after the current transition point, only the partition for the second 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, 2023. If you try to insert data for May 10, 2023, then the required partition for April 15 to May 15, 2023 is created and data is inserted into that partition. The partition for February 15, 2023 to March 15, 2023 and March 15, 2023 to April 15, 2023 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 isn't supported for index-organized tables.
  • You can't create a domain index on an interval-range-partitioned table.
  • In composite partitioning, the interval range partitioning can be useful as a primary partitioning mechanism but isn't supported at the subpartition level.
  • You can't define DEFAULT and MAXVALUE for an interval-range-partitioned table.
  • You can't specify NULL, Not-a-Number, and Infinity values in the partitioning key column.
  • Interval range partitioning expression must yield constant value and can't be a negative value.
  • You must create the partitions for an interval-range-partitioned table in increasing order.
← Prev

Selecting a partition type

Next →

Automatic list partitioning