Help Center/ GaussDB/ Centralized_8.x/ Partitioned Table/ Partitioned Table O&M Management/ SPLIT PARTITION/ Splitting a Partition for an Interval Partitioned Table
Updated on 2024-06-07 GMT+08:00
View PDF

Splitting a Partition for an Interval Partitioned Table

You can run ALTER TABLE SPLIT PARTITION to split a partition for an interval partitioned table.

After an interval partition is split, the interval partition before the split partition becomes a range partition.

For example, create the following interval partitioned table and add three partitions: sys_p1, sys_p2, and sys_p3. 
CREATE TABLE interval_sales
(
    prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10, 2)
)
PARTITION BY RANGE (TIME_ID) INTERVAL ('1 MONTH')
(
    PARTITION date_2015 VALUES LESS THAN ('2016-01-01'),
    PARTITION date_2016 VALUES LESS THAN ('2017-01-01'),
    PARTITION date_2017 VALUES LESS THAN ('2018-01-01'),
    PARTITION date_2018 VALUES LESS THAN ('2019-01-01'),
    PARTITION date_2019 VALUES LESS THAN ('2020-01-01')
);
INSERT INTO interval_sales VALUES (263722,42819872,'2020-07-09','E',432072,213,17); -- The sys_p1 partition is added.
INSERT INTO interval_sales VALUES (345724,72651233,'2021-03-05','A',352451,146,9);  -- The sys_p2 partition is added.
INSERT INTO interval_sales VALUES (153241,65143129,'2021-05-07','H',864134,89,34);  -- The sys_p3 partition is added.

If the sys_p2 partition is split, the sys_p1 partition is changed to a range partition, and the lower boundary of the partition range depends on the upper boundary of the previous partition instead of the interval partition value. That is, the partition range changes from ['2020-07-01', '2020-08-01') to ['2020-01-01', '2020-08-01'). The sys_p3 partition is still an interval partition, and its partition range is ['2021-05-01', '2021-06-01').

Parent topic: SPLIT PARTITION