Interval range partitioning is an extension of range partitioning. Interval range partitions are automatically created when data arrives. You do not need to manually create new partitions. This facilitates maintenance of range partitions.
When you insert data into a range partitioned table and if the data falls beyond the range of the existing partition, the data cannot be inserted and an error is returned. When the data to be inserted exceeds the range of an interval partitioned table, the database can automatically create new partitions based on the range specified by the INTERVAL clause.
If the partition range is set to one month and the data to be inserted is two months from the current transition point (the maximum boundary value of the current partition), the partition for the month of the data and the partition for the month before that month are created. For example, you create an interval range partitioned table. The interval is one month and the current transition point is September 15, 2021. If you attempt to insert the data of December 10, 2021, the database automatically creates three partitions for the three intervals from September 15, 2021 to December 15, 2021. Then, the data is inserted into the partition for the interval that covers December 10, 2021.
We recommend that you use interval range partitions in the following cases:
Data is maintained in the time dimension.
A tumbling window is maintained.
Manual creation of partitions is not preferred.
In the following examples, orde_time is used as the partition key to partition the sales table by interval. When you create an interval range partitioned table, you must specify an initial transition point. Then, new partitions can be automatically created beyond the transition point.
Create an interval range partitioned table in the database and populate the table with data:
CREATE TABLE sales
(
id BIGINT,
uid BIGINT,
order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
PARTITION p0 VALUES LESS THAN('2021-9-1')
);Populate the interval range partitioned table with data:
INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');After data is inserted, you can execute the SHOW CREATE TABLE statement to query the sales table definition. The following new table definition is displayed:
CREATE TABLE `sales` (
`id` bigint(20) DEFAULT NULL,
`uid` bigint(20) DEFAULT NULL,
`order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */Three partitions _p20211001000000, _p20211101000000, and _p20211201000000 are added after interval range partitioning is used.
Partition names prefixed with _p are reserved by the system. Such partition names cannot be used when you create or rename partitions. For example, partition names prefixed with '_p' cannot be used when you execute the ADD PARTITION and REORGANIZE PARTITION statements. However, such partition names can be used when you execute the DROP PARTITION statement.
In this scenario, you can manually add partitions before inserted values reach the transition point. However, data insertion may fail due to accidental operations. If you create an interval range partitioned table, partitions can be automatically created to ensure prompt data insertion.
The number of automatically created partitions is limited because a maximum of 8,192 partitions can be created. You can use interval range partitioning with the partitioned table lifecycle management solution to create partitions and migrate the partitions where cold data is located to OSS on a regular basis.