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 p_2021 partition of the split sales_range_range table into p_2021_1, p_2021_2, and subpartition them:
ALTER TABLE sales_range_range REORGANIZE PARTITION p_2021 INTO (
    PARTITION p_2021_1 VALUES LESS THAN (2021)(
    SUBPARTITION q1_2021_1 VALUES LESS THAN(4),
    SUBPARTITION q2_2021_1 VALUES LESS THAN(7),
    SUBPARTITION q3_2021_1 VALUES LESS THAN(10),
    SUBPARTITION q4_2021_1 VALUES LESS THAN(13)
  ),
    PARTITION p_2021_2 VALUES LESS THAN (2022)(
    SUBPARTITION q1_2021_2 VALUES LESS THAN(4),
    SUBPARTITION q2_2021_2 VALUES LESS THAN(7),
    SUBPARTITION q3_2021_2 VALUES LESS THAN(10),
    SUBPARTITION q4_2021_2 VALUES LESS THAN(13)
  )
);
Split the q1_2021 partition of the sales_range_hash table into q1_2021_1, q1_2021_2, and subpartition them:
ALTER TABLE sales_range_hash REORGANIZE PARTITION q1_2021 INTO (
    PARTITION q1_2021_1 VALUES LESS THAN (2)(
    SUBPARTITION s0_1 ,
    SUBPARTITION s1_1
  ),
    PARTITION q1_2021_2 VALUES LESS THAN (4)(
    SUBPARTITION s0_2 ,
    SUBPARTITION s1_2
  )
);
Reorganize partitions p0, p1, and p2 into partitions q1 and q2:
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
        )
);