Create a table with interval range partitioning - PolarDB

Interval range partitioning extends range partitioning by automatically creating new partitions as data arrives. When inserted data exceeds the high value of the last defined range partition, the database creates a new partition — no manual intervention required.

To use interval range partitioning, include an INTERVAL clause in your CREATE TABLE statement and specify the partition size. The partition key value determines the high value of each range partition, which acts as the transition point: data that exceeds this boundary triggers automatic partition creation.

How skipping works: If data lands two or more intervals beyond the current transition point, the database creates only the partition that contains the data — not the intermediate partitions.

Example: Suppose the interval is one month and the current transition point is February 15, 2019. Inserting a row dated May 10, 2019 creates the partition covering April 15–May 15, 2019. The partitions for February 15–March 15 and March 15–April 15 are skipped — only the partition that actually contains the data is created.

Syntax

CREATE TABLE [schema.]<table_name>
   <table_definition>
   PARTITION BY RANGE (<column> [, <column>]...)
   [INTERVAL (<constant> | <expression>)]
   [SUBPARTITION BY {RANGE | LIST | HASH} (<column> [, <column>]...)]
   (<range_partition_definition> [, <range_partition_definition>]...);

Where range_partition_definition is:

PARTITION [<partition_name>]
  VALUES LESS THAN (<value> [, <value>]...)
  [TABLESPACE <tablespace_name>]
  [(<subpartition>, ...)]

The INTERVAL parameter accepts numerical and time expressions:

Type	Expression	Effect
Numerical	`INTERVAL (10)`	One partition per 10 consecutive values
Year	`INTERVAL (NUMTOYMINTERVAL(1, 'year'))`	One partition per year
Month	`INTERVAL (NUMTOYMINTERVAL(1, 'month'))`	One partition per month
Day	`INTERVAL (NUMTODSINTERVAL(1, 'day'))`	One partition per day
Week	`INTERVAL (NUMTODSINTERVAL(7, 'day'))`	One partition per week

For the full CREATE TABLE ... PARTITION BY syntax, see CREATE TABLE... PARTITION BY.

Example

The following example creates a sales table partitioned by sold_month with a one-month interval. Two initial range partitions establish the transition point; rows beyond that point trigger automatic partition creation.

Create the table:

CREATE TABLE sales
(
  prod_id           int,
  prod_quantity     int,
  sold_month        date
)
PARTITION BY RANGE(sold_month)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p1
    VALUES LESS THAN('15-JAN-2019'),
  PARTITION p2
    VALUES LESS THAN('15-FEB-2019')
);

Query the initial partitions:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

 partition_name |      high_value
----------------+--------------------------------------------------------------
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(2 rows)

Insert a row beyond the transition point:

INSERT INTO sales VALUES (1, 200, '10-MAY-2019');

INSERT 0 1

Query the partitions again:

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

 partition_name |      high_value
----------------+--------------------------------------------------------------
 SYS596430103   | FOR VALUES FROM ('15-APR-19 00:00:00') TO ('15-MAY-19 00:00:00')
 P1             | FOR VALUES FROM ('15-JAN-19 00:00:00') TO ('15-FEB-19 00:00:00')
 P2             | FOR VALUES FROM (MINVALUE) TO ('15-JAN-19 00:00:00')
(3 rows)

The database automatically created one new partition to hold the May 10, 2019 row. The partitions covering February 15–March 15 and March 15–April 15 were skipped because no data fell in those ranges.

The system assigns a SYS-prefixed name (such as SYS596430103) to each automatically created partition. The name is unique but varies by session.

Restrictions on interval range partitioning

The partition key must be a single column specifying a numerical or date range.
At least one range partition must be defined before the INTERVAL clause takes effect.
The INTERVAL clause is not supported for index-organized tables.
Domain indexes cannot be created on list-partitioned tables.
The partition key column cannot contain NULL, Not-a-Number, or Infinity values.
Expressions in the INTERVAL clause must evaluate to non-negative constant values.
Interval range partitions are created in ascending order only.