Reorganize the partitions of a table - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to restructure partitions in a list-partitioned or range-partitioned table by using the REORGANIZE PARTITION clause in the ALTER TABLE statement. The system automatically redistributes data to match the new partition definitions based on the clause. No data is lost during the restructuring process.

Examples

Split partitions

-- Split the existing partition p2019_2020 into two new partitions 2019 and 2020.
ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

Merge partitions

-- Merge partitions p2021 and p2022.
ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

You can merge only partitions that have contiguous range values.

Modify partitions

-- Reorganize existing partitions into four new partitions based on quarterly ranges.
ALTER TABLE logs
REORGANIZE PARTITION p0, p1 INTO (
    PARTITION q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))
);

Syntax

ALTER TABLE table_name
    REORGANIZE PARTITION partition_names INTO (partition_definitions)

partition_definitions: {list_partition | range_partition}

subpartition_definition: {list_subpartition | range_subpartition | hash_subpartition | key_subpartition}

partition_definitions

Define a list partition.

PARTITION partition_name VALUES IN (value[, value]...) 
(subpartition, ...)

Define a range partition.

PARTITION partition_name VALUES LESS THAN (value[, value]...) 
(subpartition, ...)

subpartition_definitions

Define a list subpartition.

SUBPARTITION [subpartition_name] VALUES IN (value[, value]...) 
[TABLESPACE tablespace_name]

Define a range subpartition.

SUBPARTITION [subpartition_name] VALUES LESS THAN (value[, value]...) 
[TABLESPACE tablespace_name]

Define a hash or key subpartition.

SUBPARTITION [subpartition_name ]
[TABLESPACE tablespace_name]

Parameter	Description
table_name	The name of the table.
partition_names	A comma-separated list of existing partition names that you want to merge or split.
partition_definitions	A comma-separated list of new partition definitions.
partition_name	The name of the partition that you want to create. Note Partition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
subpartition_name	The name of the subpartition that you want to create. Note Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.

Examples

Prepare data

--Create a sales table that is range-partitioned by year and further hash-subpartitioned by quarter. 
CREATE TABLE sales_data (
    order_id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)  -- The primary key must include the partition key.
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH( QUARTER(order_date) )
SUBPARTITIONS 4 (  -- By default, each parent partition has four subpartitions.
    PARTITION p2019_2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Insert test data.
INSERT INTO sales_data (order_date, amount) VALUES
('2020-03-15', 100.00),  -- p2019_2020
('2021-06-20', 200.00),  -- p2021
('2022-09-10', 300.00),  -- p2022
('2023-12-25', 400.00);  -- p_future

Split partitions

Split the p2019_2020 partition into p2019 and p2020 partitions.

ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

Merge partitions

Merge the p2021 and p2022 partitions into a single p2021_2022 partition.

ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

Modify partitions

Modify the four partitions p2019, p2020, p2021_2022, and p_future into three partitions p2019, p2020_2021, and p2022_future.

ALTER TABLE sales_data
REORGANIZE PARTITION p2019, p2020, p2021_2022, p_future INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020_2021 VALUES LESS THAN (2022) (
        SUBPARTITION s2020_2021_q1,
        SUBPARTITION s2020_2021_q2,
        SUBPARTITION s2020_2021_q3,
        SUBPARTITION s2020_2021_q4
    ),
    PARTITION p2022_future VALUES LESS THAN MAXVALUE (
        SUBPARTITION s2022_future_1,
        SUBPARTITION s2022_future_2,
        SUBPARTITION s2022_future_3,
        SUBPARTITION s2022_future_4
    )
);