REORGANIZE PARTITION - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to reorganize some partitions or all partitions of a table and all their subpartitions without data loss.

Syntax

The ALTER TABLE…REORGANIZE PARTITION statement is used to reorganize some partitions or all partitions of a table and all their subpartitions without data loss.

ALTER TABLE table_name
    REORGANIZE PARTITION partition_names INTO (partition_definitions)

partition_definitions is:

{list_partition | range_partition}

list_partition is:

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

range_partition is:

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

subpartition is:

{list_subpartition | range_subpartition | hash_partition | key_partition}

list_subpartition is:

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

range_subpartition is:

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

hash/key_subpartition is:

SUBPARTITION [subpartition_name ]
[TABLESPACE tablespace_name]

Parameters

Parameter	Description
table_name	The name of the table.
list_partition	The name list of one or more existing partitions to be modified. Separate multiple partition names with commas (,).
partition_definitions	The list of new partition definitions. Separate multiple partition definitions with commas (,).
partition_name	The name of the partition. Note The partition name must be different from the names of all existing partitions and subpartitions, and follow the naming conventions for object identifiers.
subpartition_name	The name of the subpartition. Note The subpartition name must be different from the names of all existing partitions and subpartitions, and follow the naming conventions for object identifiers.

Examples

Split the p0 partition of the split sales_range_range table into p0_1, p0_2, and subpartition them:

ALTER TABLE sales_range_range REORGANIZE PARTITION p0 INTO (
    PARTITION p0_1 VALUES LESS THAN (500)(
    SUBPARTITION q1_2021_1 VALUES LESS THAN(200),
    SUBPARTITION q2_2021_1 VALUES LESS THAN(300),
    SUBPARTITION q3_2021_1 VALUES LESS THAN(400),
    SUBPARTITION q4_2021_1 VALUES LESS THAN(500)
  ),
    PARTITION p0_2 VALUES LESS THAN (1000)(
    SUBPARTITION q1_2021_2 VALUES LESS THAN(600),
    SUBPARTITION q2_2021_2 VALUES LESS THAN(700),
    SUBPARTITION q3_2021_2 VALUES LESS THAN(800),
    SUBPARTITION q4_2021_2 VALUES LESS THAN(1000)
  )
);

Split the p1 partition of the sales_range_hash table into p1_1, p1_2, and subpartition them:

ALTER TABLE sales_range_hash REORGANIZE PARTITION p1 INTO (
    PARTITION p1_1 VALUES LESS THAN ('2022-12-01')(
    SUBPARTITION s0_1 VALUES LESS THAN ('2022-12-05'),
    SUBPARTITION s1_1 VALUES LESS THAN ('2022-12-10'),
    SUBPARTITION s2_1 VALUES LESS THAN ('2022-12-15')
  ),
    PARTITION p1_2 VALUES LESS THAN ('2023-01-01')(
    SUBPARTITION s0_2 VALUES LESS THAN ('2022-12-20'),
    SUBPARTITION s1_2 VALUES LESS THAN ('2022-12-25'),
    SUBPARTITION s2_2 VALUES LESS THAN ('2022-12-30')
  )
);

Reorganize partitions p0, p1, and p2 into partitions q1, q2,and q3:

ALTER TABLE sales_list_hash REORGANIZE PARTITION p0,p1,p2 INTO (
             PARTITION q1 VALUES in (1,2)(
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION q2 VALUES in (3,4)(
            SUBPARTITION s3,
            SUBPARTITION s4
        ),
        PARTITION q3 VALUES in (5,6)(
            SUBPARTITION s5,
            SUBPARTITION s6
        )
);