PolarDB:Switch between range partitioning and interval range partitioning

Convert a range-partitioned table to an interval range-partitioned table

ALTER TABLE <table_name> SET INTERVAL (<constant> | <expression>);

After the conversion, the database automatically creates a new partition whenever inserted data falls outside the existing partition boundaries. For details on the INTERVAL parameter, see Interval range partitioning.

Revert an interval range-partitioned table to a range-partitioned table

ALTER TABLE <table_name> SET INTERVAL ();

Calling SET INTERVAL with empty parentheses disables interval range partitioning. The database changes the interval range-partitioned table back to a range-partitioned table, and the interval range partitioning boundary is changed to a range partitioning boundary.

Example

The following example converts the sales table from range partitioning to interval range partitioning, inserts data that exceeds the existing partition boundaries, and verifies that the database automatically creates a new partition.

Step 1: Create a range-partitioned table.

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

Step 2: Query the initial partition layout.

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 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)

Step 3: Convert to interval range partitioning with a monthly interval.

ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

Step 4: Insert data that falls outside the existing partition boundaries.

INSERT INTO sales VALUES (1,100,'05-APR-2019');
INSERT 0 1

Step 5: Query the partition layout again.

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Output:

 partition_name |      high_value
----------------+----------------------
 SYS596430103   | FOR VALUES FROM ('15-MAR-19 00:00:00') TO ('15-APR-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 a new partition (SYS596430103) to accommodate the inserted row. The partition name is system-generated and varies across sessions.

What's next

• Interval range partitioning