Merge and Split RANGE-LIST Partitions in PolarDB-X - PolarDB-X

Use REORGANIZE PARTITION to merge multiple partitions into fewer ones, or split a single partition into more — without data loss. PolarDB-X redistributes existing rows into the new partition definitions automatically.

Prerequisites

Before you begin, make sure you have:

A PolarDB-X instance at version 5.4.14-16539836 or later for level-1 partition reorganization
A PolarDB-X instance at version 5.4.17-16952556 or later for level-2 partition reorganization

Limitations

Constraint	Details
Unsupported partition types	HASH, KEY, and CO_HASH partitions cannot be reorganized.
Boundary coverage	The new partition definitions must cover exactly the same boundary value range as the original partitions. For RANGE partitions, the final `VALUES LESS THAN` bound must match that of the last original partition. For LIST partitions, the union of all `VALUES IN` sets must be identical.
Non-templated level-2 constraint	When reorganizing level-1 partitions that contain non-templated level-2 partitions, the `INTO` clause must also define non-templated level-2 partitions.
Templated level-2 scope	Reorganizing templated level-2 partitions applies the change across all level-1 partitions simultaneously.

Key concepts

Table group: A collection of logical tables or global index tables that share identical partition key columns. Reorganizing a table group applies the operation to all tables in the group at once.

Global index: An index on a partitioned table built on non-partition keys. Global indexes can enforce unique constraints across all partitions.

Level-1 partition: The top-level partition in a multi-level partitioned table.

Level-2 partition (subpartition): A nested partition within a level-1 partition. Can be templated (a single definition applied to all level-1 partitions) or non-templated (defined individually per level-1 partition).

Syntax

ALTER alter_target_definition reorg_partition_operation_definition

alter_target_definition:
    TABLE [db_name.]tbl_name
  | TABLE tbl_name.index_name
  | TABLEGROUP tg_name
  | INDEX index_name ON TABLE [db_name.]tbl_name
  | TABLEGROUP BY TABLE [db_name.]tbl_name
  | TABLEGROUP BY INDEX index_name ON TABLE [db_name.]tbl_name

reorg_partition_operation_definition:
    REORGANIZE PARTITION part_name_list INTO (partition_specs_definition)
  | REORGANIZE SUBPARTITION subpart_name_list INTO (subpartition_specs_definition)
  | MODIFY PARTITION part_name REORGANIZE SUBPARTITION subpart_name_list INTO (subpartition_specs_definition)

part_name_list:
  part_name [, part_name, ...]

subpart_name_list:
  subpart_name [, subpart_name, ...]

partition_specs_definition:
    range_partition_list
  | list_partition_list

subpartition_specs_definition:
    range_subpartition_list
  | list_subpartition_list

range_partition_list:
  (range_partition [, range_partition, ...])

range_partition:
  PARTITION partition_name VALUES LESS THAN (range_bound_value)

-- Specify RANGE or RANGE COLUMNS level-2 partitions.
range_subpartition_list:
  (range_subpartition [, range_subpartition, ...])

range_subpartition:
  SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]

list_partition_list:
  (list_partition [, list_partition, ...])

list_partition:
  PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options]

-- Specify LIST or LIST COLUMNS level-2 partitions.
list_subpartition_list:
  (list_subpartition [, list_subpartition, ...])

list_subpartition:
  SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

Each ALTER statement targets either a single table, a named table group, or a global index. Use REORGANIZE PARTITION for level-1 partitions and REORGANIZE SUBPARTITION for level-2 partitions.

Scenario 1: Reorganize level-1 partitions (no level-2 partitions)

Example 1: Merge and split RANGE level-1 partitions

The following tables are partitioned by RANGE(YEAR(c)) across four partitions:

CREATE TABLE `r_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN (2023),
  PARTITION p2 VALUES LESS THAN (2025),
  PARTITION p3 VALUES LESS THAN (2027)
);

CREATE TABLE `r_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN (2023),
  PARTITION p2 VALUES LESS THAN (2025),
  PARTITION p3 VALUES LESS THAN (2027)
);

The following statements merge partitions p1, p2, and p3 (covering years 2020–2027) into two new partitions p4 and p5. The combined range of the new partitions matches the original: p4 covers up to 2024, p5 covers up to 2027.

Target a single table by table name:

ALTER TABLE r_t1
  REORGANIZE PARTITION p1, p2, p3 INTO
  (
    PARTITION p4 VALUES LESS THAN (2024),
    PARTITION p5 VALUES LESS THAN (2027)
  );

Target all tables in a table group by table group name:

-- Applies to all tables in table group tg1, including r_t1 and r_t2.
ALTER TABLEGROUP tg1
  REORGANIZE PARTITION p1, p2, p3 INTO
  (
    PARTITION p4 VALUES LESS THAN (2024),
    PARTITION p5 VALUES LESS THAN (2027)
  );

Target all tables in a table group by table name:

-- PolarDB-X automatically identifies the table group from the table name
-- and applies the reorganization to all tables in that group.
ALTER TABLEGROUP BY TABLE r_t1
  REORGANIZE PARTITION p1, p2, p3 INTO
  (
    PARTITION p4 VALUES LESS THAN (2024),
    PARTITION p5 VALUES LESS THAN (2027)
  );

Example 2: Merge and split LIST level-1 partitions

The following tables are partitioned by LIST(YEAR(c)):

CREATE TABLE `l_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(YEAR(`c`))
(
  PARTITION p0 VALUES IN (2020, 2022, 2024, 2026, 2028),
  PARTITION p1 VALUES IN (2021, 2023, 2025, 2027, 2029),
  PARTITION p2 VALUES IN (2030, 2031, 2032)
);

CREATE TABLE `l_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(YEAR(`c`))
(
  PARTITION p0 VALUES IN (2020, 2022, 2024, 2026, 2028),
  PARTITION p1 VALUES IN (2021, 2023, 2025, 2027, 2029),
  PARTITION p2 VALUES IN (2030, 2031, 2032)
);

The following statements merge p0 and p1 (covering years 2020–2029) into p3 and p4, grouping years consecutively. The union of the new VALUES IN sets matches the original exactly.

Target a single table:

ALTER TABLE l_t1
  REORGANIZE PARTITION p0, p1 INTO
  (
    PARTITION p3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    PARTITION p4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE PARTITION p0, p1 INTO
  (
    PARTITION p3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    PARTITION p4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE l_t1
  REORGANIZE PARTITION p0, p1 INTO
  (
    PARTITION p3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    PARTITION p4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Scenario 2: Reorganize level-1 partitions that contain level-2 partitions

Example 1: Reorganize level-1 partitions and define new level-2 partitions

This applies only to tables with non-templated level-2 partitions. The new partition definitions in the INTO clause must also be non-templated.

The following RANGE-KEY partitioned tables have non-templated level-2 partitions:

CREATE TABLE `r_k_ntp_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY KEY(a)
(
  PARTITION p0 VALUES LESS THAN (2020) SUBPARTITIONS 2,
  PARTITION p1 VALUES LESS THAN (2021) SUBPARTITIONS 4,
  PARTITION p2 VALUES LESS THAN (2022) SUBPARTITIONS 2,
  PARTITION p3 VALUES LESS THAN (2023) SUBPARTITIONS 3
);

CREATE TABLE `r_k_ntp_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY KEY(a)
(
  PARTITION p0 VALUES LESS THAN (2020) SUBPARTITIONS 2,
  PARTITION p1 VALUES LESS THAN (2021) SUBPARTITIONS 4,
  PARTITION p2 VALUES LESS THAN (2022) SUBPARTITIONS 2,
  PARTITION p3 VALUES LESS THAN (2023) SUBPARTITIONS 3
);

The following statements merge p1 and p2 into a new partition p4 with 1 subpartition.

Target a single table:

ALTER TABLE r_k_ntp_t1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE r_k_ntp_t1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
  );

Example 2: Reorganize level-1 partitions without redefining level-2 partitions

This applies to both templated and non-templated level-2 partitions.

Target a single table:

ALTER TABLE r_k_ntp_t1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE r_k_ntp_t1
  REORGANIZE PARTITION p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (2022)
  );

Scenario 3: Reorganize templated level-2 partitions

Reorganizing templated level-2 partitions applies the change to the corresponding subpartitions under all level-1 partitions simultaneously.

Example 1: Merge and split RANGE level-2 partitions

The following KEY-RANGE partitioned tables have templated level-2 partitions:

CREATE TABLE `k_r_tp_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY RANGE(`b`)
(
  SUBPARTITION sp0 VALUES LESS THAN (1000),
  SUBPARTITION sp1 VALUES LESS THAN (2000),
  SUBPARTITION sp2 VALUES LESS THAN (3000),
  SUBPARTITION sp3 VALUES LESS THAN (4000)
);

CREATE TABLE `k_r_tp_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY RANGE(`b`)
(
  SUBPARTITION sp0 VALUES LESS THAN (1000),
  SUBPARTITION sp1 VALUES LESS THAN (2000),
  SUBPARTITION sp2 VALUES LESS THAN (3000),
  SUBPARTITION sp3 VALUES LESS THAN (4000)
);

The following statements split sp1 and sp2 (covering values 1000–3000) into three subpartitions sp4, sp5, and sp6. The same reorganization is applied under every level-1 partition.

Target a single table:

ALTER TABLE k_r_tp_t1
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION sp4 VALUES LESS THAN (2500),
    SUBPARTITION sp5 VALUES LESS THAN (2800),
    SUBPARTITION sp6 VALUES LESS THAN (3000)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION sp4 VALUES LESS THAN (2500),
    SUBPARTITION sp5 VALUES LESS THAN (2800),
    SUBPARTITION sp6 VALUES LESS THAN (3000)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE k_r_tp_t1
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION sp4 VALUES LESS THAN (2500),
    SUBPARTITION sp5 VALUES LESS THAN (2800),
    SUBPARTITION sp6 VALUES LESS THAN (3000)
  );

Example 2: Merge and split LIST level-2 partitions

The following KEY-LIST partitioned tables have templated level-2 partitions:

CREATE TABLE `k_l_tp_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
  SUBPARTITION sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
  SUBPARTITION sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
  SUBPARTITION sp2 VALUES IN (2030, 2031, 2032)
);

CREATE TABLE `k_l_tp_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`) PARTITIONS 2
SUBPARTITION BY LIST(YEAR(`c`))
(
  SUBPARTITION sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
  SUBPARTITION sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
  SUBPARTITION sp2 VALUES IN (2030, 2031, 2032)
);

The following statements merge sp0 and sp1 (covering years 2020–2029) into two new subpartitions grouped consecutively.

Target a single table:

ALTER TABLE k_l_tp_t1
  REORGANIZE SUBPARTITION sp0, sp1 INTO
  (
    SUBPARTITION sp3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION sp4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE SUBPARTITION sp0, sp1 INTO
  (
    SUBPARTITION sp3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION sp4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE k_l_tp_t1
  REORGANIZE SUBPARTITION sp0, sp1 INTO
  (
    SUBPARTITION sp3 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION sp4 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Scenario 4: Reorganize non-templated level-2 partitions

For non-templated level-2 partitions, you can reorganize subpartitions under a specific level-1 partition independently of the others.

Example 1: Reorganize RANGE level-2 partitions under a specified level-1 partition

The following LIST-RANGE partitioned tables have non-templated level-2 partitions:

CREATE TABLE `l_r_ntp_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(`a`)
SUBPARTITION BY RANGE(`b`)
(
  PARTITION p0 VALUES IN (2020, 2022) (
    SUBPARTITION p0sp0 VALUES LESS THAN (1000),
    SUBPARTITION p0sp1 VALUES LESS THAN (2000),
    SUBPARTITION p0sp2 VALUES LESS THAN (3000),
    SUBPARTITION p0sp3 VALUES LESS THAN (4000)
  ),
  PARTITION p1 VALUES IN (2021, 2023) (
    SUBPARTITION p1sp0 VALUES LESS THAN (1500),
    SUBPARTITION p1sp1 VALUES LESS THAN (2500),
    SUBPARTITION p1sp2 VALUES LESS THAN (3500),
    SUBPARTITION p1sp3 VALUES LESS THAN (4500)
  )
);

CREATE TABLE `l_r_ntp_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY LIST(`a`)
SUBPARTITION BY RANGE(`b`)
(
  PARTITION p0 VALUES IN (2020, 2022) (
    SUBPARTITION p0sp0 VALUES LESS THAN (1000),
    SUBPARTITION p0sp1 VALUES LESS THAN (2000),
    SUBPARTITION p0sp2 VALUES LESS THAN (3000),
    SUBPARTITION p0sp3 VALUES LESS THAN (4000)
  ),
  PARTITION p1 VALUES IN (2021, 2023) (
    SUBPARTITION p1sp0 VALUES LESS THAN (1500),
    SUBPARTITION p1sp1 VALUES LESS THAN (2500),
    SUBPARTITION p1sp2 VALUES LESS THAN (3500),
    SUBPARTITION p1sp3 VALUES LESS THAN (4500)
  )
);

The following statements split p0sp1 and p0sp2 (covering values 1000–3000) under partition p0 into three subpartitions. Only the subpartitions under p0 are affected; p1 is unchanged.

Target a single table:

ALTER TABLE l_r_ntp_t1
  REORGANIZE SUBPARTITION p0sp1, p0sp2 INTO
  (
    SUBPARTITION p0sp4 VALUES LESS THAN (2500),
    SUBPARTITION p0sp5 VALUES LESS THAN (2800),
    SUBPARTITION p0sp6 VALUES LESS THAN (3000)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE SUBPARTITION p0sp1, p0sp2 INTO
  (
    SUBPARTITION p0sp4 VALUES LESS THAN (2500),
    SUBPARTITION p0sp5 VALUES LESS THAN (2800),
    SUBPARTITION p0sp6 VALUES LESS THAN (3000)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE l_r_ntp_t1
  REORGANIZE SUBPARTITION p0sp1, p0sp2 INTO
  (
    SUBPARTITION p0sp4 VALUES LESS THAN (2500),
    SUBPARTITION p0sp5 VALUES LESS THAN (2800),
    SUBPARTITION p0sp6 VALUES LESS THAN (3000)
  );

Example 2: Reorganize LIST level-2 partitions under a specified level-1 partition

The following RANGE-LIST partitioned tables have non-templated level-2 partitions:

CREATE TABLE `r_l_ntp_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY LIST(`b`)
(
  PARTITION p0 VALUES LESS THAN (2020) (
    SUBPARTITION p0sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
    SUBPARTITION p0sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
    SUBPARTITION p0sp2 VALUES IN (2030, 2031)
  ),
  PARTITION p1 VALUES LESS THAN (2022) (
    SUBPARTITION p1sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
    SUBPARTITION p1sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
    SUBPARTITION p1sp2 VALUES IN (2030)
  )
);

CREATE TABLE `r_l_ntp_t2` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
SUBPARTITION BY LIST(`b`)
(
  PARTITION p0 VALUES LESS THAN (2020) (
    SUBPARTITION p0sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
    SUBPARTITION p0sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
    SUBPARTITION p0sp2 VALUES IN (2030, 2031)
  ),
  PARTITION p1 VALUES LESS THAN (2022) (
    SUBPARTITION p1sp0 VALUES IN (2020, 2022, 2024, 2026, 2028),
    SUBPARTITION p1sp1 VALUES IN (2021, 2023, 2025, 2027, 2029),
    SUBPARTITION p1sp2 VALUES IN (2030)
  )
);

The following statements merge p0sp0 and p0sp1 (covering years 2020–2029) under partition p0 into two new subpartitions. The subpartitions under p1 are not affected.

Target a single table:

ALTER TABLE r_l_ntp_t1
  REORGANIZE SUBPARTITION p0sp0, p0sp1 INTO
  (
    SUBPARTITION p0sp4 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION p0sp5 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table group name:

ALTER TABLEGROUP tg1
  REORGANIZE SUBPARTITION p0sp0, p0sp1 INTO
  (
    SUBPARTITION p0sp4 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION p0sp5 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Target all tables in a table group by table name:

ALTER TABLEGROUP BY TABLE r_l_ntp_t1
  REORGANIZE SUBPARTITION p0sp0, p0sp1 INTO
  (
    SUBPARTITION p0sp4 VALUES IN (2020, 2021, 2022, 2023, 2024),
    SUBPARTITION p0sp5 VALUES IN (2025, 2026, 2027, 2028, 2029)
  );

Scenario 5: Reorganize partitions of a global index table

Example 1: Reorganize RANGE level-1 partitions of a global index

The table r_gr_t1 has a global index g_r partitioned by RANGE(a):

CREATE TABLE `r_gr_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL,
  GLOBAL INDEX `g_r` (`a`) COVERING (`c`)
    PARTITION BY RANGE(`a`)
    (
      PARTITION `p0` VALUES LESS THAN (10000),
      PARTITION `p1` VALUES LESS THAN (20000),
      PARTITION `p2` VALUES LESS THAN (30000),
      PARTITION `p3` VALUES LESS THAN (40000)
    )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
  PARTITION `p0` VALUES LESS THAN (2020),
  PARTITION `p1` VALUES LESS THAN (2021)
);

The following statements merge p0, p1, and p2 (covering values up to 30000) in the g_r global index into two new partitions p4 and p5.

Target a global index by table name and index name:

-- Use the table_name.index_name form.
ALTER TABLE `r_gr_t1`.`g_r`
  REORGANIZE PARTITION p0, p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (15000),
    PARTITION p5 VALUES LESS THAN (30000)
  );

-- Alternatively, use INDEX index_name ON TABLE table_name.
ALTER INDEX `g_r` ON TABLE `r_gr_t1`
  REORGANIZE PARTITION p0, p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (15000),
    PARTITION p5 VALUES LESS THAN (30000)
  );

Target all global index tables in a table group by table group name:

-- tg1 is the table group to which the global index tables belong.
ALTER TABLEGROUP tg1
  REORGANIZE PARTITION p0, p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (15000),
    PARTITION p5 VALUES LESS THAN (30000)
  );

Target all global index tables in a table group by index name and table name:

ALTER TABLEGROUP BY INDEX `g_r` ON TABLE `r_gr_t1`
  REORGANIZE PARTITION p0, p1, p2 INTO
  (
    PARTITION p4 VALUES LESS THAN (15000),
    PARTITION p5 VALUES LESS THAN (30000)
  );

Example 2: Reorganize LIST level-2 partitions of a global index

The table r_g_kl_t1 has a global index g_kl using KEY-LIST partitioning with templated level-2 partitions:

CREATE TABLE `r_g_kl_t1` (
  `a` bigint(20) UNSIGNED NOT NULL,
  `b` bigint(20) UNSIGNED NOT NULL,
  `c` datetime NOT NULL,
  `d` varchar(16) NOT NULL,
  `e` varchar(16) NOT NULL,
  GLOBAL INDEX `g_kl` (`a`, `b`) COVERING (`c`)
    PARTITION BY KEY(`b`) PARTITIONS 2
    SUBPARTITION BY LIST(`a`)
    (
      SUBPARTITION `sp0` VALUES IN (10000, 10002, 10004, 10006, 10008),
      SUBPARTITION `sp1` VALUES IN (20000, 20001, 20002, 20003, 20004),
      SUBPARTITION `sp2` VALUES IN (30000, 30001, 30002)
    )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY RANGE(YEAR(`c`))
(
  PARTITION `p0` VALUES LESS THAN (2020),
  PARTITION `p1` VALUES LESS THAN (2021)
);

The following statements merge sp1 and sp2 (covering values 20000–30002) in the g_kl global index into two new subpartitions sp4 and sp5.

Target a global index by table name and index name:

ALTER TABLE `r_g_kl_t1`.`g_kl`
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION `sp4` VALUES IN (20000, 20001, 20002),
    SUBPARTITION `sp5` VALUES IN (20003, 20004, 30000, 30001, 30002)
  );

ALTER INDEX g_kl ON TABLE `r_g_kl_t1`
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION `sp4` VALUES IN (20000, 20001, 20002),
    SUBPARTITION `sp5` VALUES IN (20003, 20004, 30000, 30001, 30002)
  );

Target all global index tables in a table group by table group name:

-- tg1 is the table group to which the global index tables belong.
ALTER TABLEGROUP tg1
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION `sp4` VALUES IN (20000, 20001, 20002),
    SUBPARTITION `sp5` VALUES IN (20003, 20004, 30000, 30001, 30002)
  );

Target all global index tables in a table group by index name and table name:

ALTER TABLEGROUP BY INDEX `g_kl` ON TABLE `r_g_kl_t1`
  REORGANIZE SUBPARTITION sp1, sp2 INTO
  (
    SUBPARTITION `sp4` VALUES IN (20000, 20001, 20002),
    SUBPARTITION `sp5` VALUES IN (20003, 20004, 30000, 30001, 30002)
  );