Interval range partitioning - PolarDB - Alibaba Cloud Documentation Center

Interval range partitioning is an extension of range partitioning. It allows a database to automatically create a partition when data to be inserted exceeds the range of an existing partition.

To implement interval range partitioning, you must include an INTERVAL clause and specify the range size for a new partition. The range partitioning key value determines the high value of a range partition. When the value of the data to be inserted exceeds the specified high value, the database creates a partition to store the new data.

Assume that you set the interval to one month. If the data to be inserted refers to the data generated two months after the current transition point, only the partition for the month to which the data belongs is created. The partition for the month in between is not created. A transition point is a key value. When data exceeds the transition point, the data is inserted into the next interval range partition. Assume that you create an interval range-partitioned table. The interval is one month and the current transition point is February 15, 2019. If you attempt to insert data for May 10, 2019, the database creates a partition for April 15, 2019 to May 15, 2019. Then, data is inserted into this partition. The partition for February 15, 2019 to March 15, 2019 and the partition for March 15, 2019 to April 15, 2019 are skipped.

Limits

Interval range partitioning is restricted to a single partition key. This key must specify a numerical or date range.
You must define at least one range partition.
You cannot use INTERVAL clauses for index-organized tables.
You cannot create domain indexes on list-partitioned tables.
You cannot specify NULL, Not-a-Number, or Infinity values in a partition key column.
The expressions that are used to implement interval range partitioning must yield non-negative constant values.
The partitions for an interval range-partitioned table can be created only in ascending order.

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 supports only numerical and time intervals.

Numerical interval
The following statement inserts every 10 adjacent numbers into one partition:
```
INTERVAL (10)
```
Time interval
- Year
  The following statement sets automatic partitioning by year:
```
INTERVAL (NUMTOYMINTERVAL(1,'year'))
```
- Month
  The following statement sets automatic partitioning by month:
```
INTERVAL (NUMTOYMINTERVAL(1,'month'))
```
- Day
  The following statement sets automatic partitioning by day:
```
INTERVAL (NUMTODSINTERVAL(1,'day'))
```
- Week
  The following statement sets automatic partitioning by week:
```
INTERVAL (NUMTODSINTERVAL(7,'day'))
```

For information about other parameters, see CREATE TABLE... PARTITION BY.

Examples

In the following example, the sales table is partitioned based on the sold_month column at specified intervals. The range partition is created to establish a transition point. Values that beyond the transition point are inserted into a new partition.

Create an interval range-partitioned table and add data to the table, as shown in the following example:

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 ALL_TAB_PARTITIONS view, as shown in the following example:

SELECT partition_name, high_value from ALL_TAB_PARTITIONS;

The following result is returned:

 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 the data that exceeds the high value of the range partition to the sales table, as shown in the following example:

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

After you insert the data, query the ALL_TAB_PARTITIONS view again, as shown in the following example:

SELECT partition_name, high_value from ALL_TAB_PARTITIONS;

If the data is inserted, the system generates a name for the interval range partition. The name varies based on sessions. The following result is returned:

 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)