Usage notes
To reorganize level-1 partitions, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.
To reorganize level-2 partitions, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.
You cannot reorganize partitions in tables that are partitioned by using the HASH, KEY, or CO_HASH partitioning method.
When you reorganize partitions, the boundary value range of the new partition must be consistent with that of the original partitions that are reorganized.
Terms
Table group: a collection of logical tables or global index tables that share identical partition key columns.
Global index: Global indexing is an indexing technique for partitioned tables. You can create global indexes on partitioned tables by using non-partition keys. Global indexes can provide unique constraints.
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 the RANGE or RANGE COLUMNS level-2 partition.
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 the LIST or LIST COLUMNS level-2 partition.
list_subpartition_list:
(list_subpartition [, list_subpartition ...])
list_subpartition:
SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]Scenario 1: Reorganize level-1 partitions that do not contain level-2 partitions
Example 1: Reorganize multiple RANGE level-1 partitions
In this example, the r_t1 and r_t2 tables are partitioned by using the RANGE partitioning method. You can execute the following SQL statements to create the tables:
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)
);You can execute the following statement to reorganize three consecutive partitions p1, p2, and p3 of the r_t1 table into the p4 and p5 partitions. The p4 and p5 partitions are defined by using the (PARTITION p4 VALUES LESS THAN (2024),PARTITION p5 VALUES LESS THAN (2027)) clauses. The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE r_t1 /*Table name*/
REORGANIZE PARTITION p1,p2,p3 INTO
(
PARTITION p4 VALUES LESS THAN (2024),
PARTITION p5 VALUES LESS THAN (2027)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the r_t1 and r_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name. In this example, the table group name is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE PARTITION p1,p2,p3 INTO
(
PARTITION p4 VALUES LESS THAN (2024),
PARTITION p5 VALUES LESS THAN (2027)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE r_t1 /*Table name*/
REORGANIZE PARTITION p1,p2,p3 INTO
(
PARTITION p4 VALUES LESS THAN (2024),
PARTITION p5 VALUES LESS THAN (2027)
);Example 2: Reorganize multiple LIST level-1 partitions
In this example, the l_t1 and l_t2 tables are partitioned by using the LIST partitioning method. You can execute the following statements to create the tables:
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)
);You can execute the following statement to reorganize the p0 and p1 level-1 partitions of the l_t1 table into the p3 and p4 level-1 partitions. The p3 and p4 level-1 partitions are defined by using the ( PARTITION p3 VALUES IN (2020,2021,2022,2023,2024), PARTITION p4 VALUES IN (2025,2026,2027,2028,2029)) clauses. The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE l_t1 /*Table name*/
REORGANIZE PARTITION p0,p1 INTO
(
PARTITION p3 VALUES IN (2020,2021,2022,2023,2024),
PARTITION p4 VALUES IN (2025,2026,2027,2028,2029)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the l_t1 and l_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE PARTITION p0,p1 INTO
(
PARTITION p3 VALUES IN (2020,2021,2022,2023,2024),
PARTITION p4 VALUES IN (2025,2026,2027,2028,2029)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE l_t1 /*Table name*/
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 multiple level-1 partitions and define level-2 partitions
You can reorganize only level-1 partitions that contain non-templated level-2 partitions and define non-templated level-2 partitions.
In this example, the partitioned tables named r_k_ntp_t1 and r_k_ntp_t2 contain non-templated level-2 partitions. You can execute the following SQL statements to create the tables:
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
);
You can execute the following statement to reorganize the p1 and p2 level-1 partitions of the r_k_ntp_t1 table into the p4 level-1 partition. The p4 level-1 partition is defined by using the PARTITION p2 VALUES LESS THAN (2022) SUBPARTITIONS 4 clause. The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE r_k_ntp_t1 /*Table name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the r_k_ntp_t1 and r_k_ntp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE r_k_ntp_t1 /*Table name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
);Example 2: Reorganize multiple level-1 partitions without defining level-2 partitions
This reorganization method applies to both tables that contain templated or non-templated level-2 partitions.
The operations in this example are similar to those in the "Scenario 1: Reorganize level-1 partitions that do not contain level-2 partitions" section. The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE r_k_ntp_t1 /*Table name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the r_k_ntp_t1 and r_k_ntp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE r_k_ntp_t1 /*Table name*/
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (2022)
);Scenario 3: Reorganize templated level-2 partitions
If you reorganize multiple templated level-2 partitions, the corresponding level-2 partitions under all level-1 partitions are reorganized at the same time.
Example 1: Reorganize multiple RANGE level-2 partitions
In this example, the KEY-RANGE partitioned tables named k_r_tp_t1 and k_r_tp_t2 contain templated level-2 partitions. You can execute the following SQL statement to create the tables:
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)
);You can execute the corresponding statement to reorganize the sp1 and sp2 level-2 partitions of the k_r_tp_t1 table into the sp4, sp5, and sp6 level-1 partitions. The sp4, sp5, and sp6 level-1 partitions are defined by using the following clauses:
(
SUBPARTITION sp4 VALUES LESS THAN (2500),
SUBPARTITION sp5 VALUES LESS THAN (2800),
SUBPARTITION sp6 VALUES LESS THAN (3000)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE k_r_tp_t1 /*Table name*/
REORGANIZE SUBPARTITION sp1,sp2 INTO
(
SUBPARTITION sp4 VALUES LESS THAN (2500),
SUBPARTITION sp5 VALUES LESS THAN (2800),
SUBPARTITION sp6 VALUES LESS THAN (3000)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the k_r_tp_t1 and k_r_tp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE SUBPARTITION sp1,sp2 INTO
(
SUBPARTITION sp4 VALUES LESS THAN (2500),
SUBPARTITION sp5 VALUES LESS THAN (2800),
SUBPARTITION sp6 VALUES LESS THAN (3000)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE k_r_tp_t1 /*Table name*/
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: Reorganize multiple LIST level-2 partitions
In this example, the k_l_tp_t1 and k_l_tp_t2 tables are partitioned by using the LIST partitioning method. You can execute the following SQL statement to create the tables:
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)
);You can execute the corresponding statement to reorganize the sp0 and sp1 templated level-2 partitions of the k_l_tp_t1 table into the sp3 and sp4 level-2 partitions. The sp3 and sp4 level-2 partitions are defined by using the following clauses:
(
SUBPARTITION sp3 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION sp4 VALUES IN (2025,2026,2027,2028,2029)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE k_l_tp_t1 /*Table name*/
REORGANIZE SUBPARTITION sp0,sp1 INTO
(
SUBPARTITION sp3 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION sp4 VALUES IN (2025,2026,2027,2028,2029)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the k_l_tp_t1 and k_l_tp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE SUBPARTITION sp0,sp1 INTO
(
SUBPARTITION sp3 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION sp4 VALUES IN (2025,2026,2027,2028,2029)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE k_l_tp_t1 /*Table name*/
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 individually reorganize level-2 partitions under a single level-1 partition.
Example 1: Reorganize multiple RANGE level-2 partitions under a specified level-1 partition
In this example, the LIST-RANGE partitioned tables named l_r_ntp_t1 and l_r_ntp_t2 contain non-templated level-2 partitions. You can execute the following statements to create the tables:
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)
)
);You can execute the corresponding statement to reorganize the p0sp1 and p0sp2 level-2 partitions under the p0 level-1 partition of the l_r_ntp_t1 table into the p0sp4, p0sp5, and p0sp6 level-2 partitions. The p0sp4, p0sp5, and p0sp6 level-2 partitions are defined by using the following clauses:
(
SUBPARTITION p0sp4 VALUES LESS THAN (2500),
SUBPARTITION p0sp5 VALUES LESS THAN (2800),
SUBPARTITION p0sp6 VALUES LESS THAN (3000)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE l_r_ntp_t1 /*Table name*/
REORGANIZE SUBPARTITION p0sp1,p0sp2 INTO
(
SUBPARTITION p0sp4 VALUES LESS THAN (2500),
SUBPARTITION p0sp5 VALUES LESS THAN (2800),
SUBPARTITION p0sp6 VALUES LESS THAN (3000)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the l_r_ntp_t1 and l_r_ntp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE SUBPARTITION p0sp1,p0sp2 INTO
(
SUBPARTITION p0sp4 VALUES LESS THAN (2500),
SUBPARTITION p0sp5 VALUES LESS THAN (2800),
SUBPARTITION p0sp6 VALUES LESS THAN (3000)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY l_r_ntp_t1 /*Table name*/
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 multiple LIST level-2 partitions under a specified level-1 partition
In this example, the LIST-RANGE partitioned tables named r_l_ntp_t1 and r_l_ntp_t2 contain non-templated level-2 partitions. You can execute the following statements to create the tables:
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)
)
);You can execute the corresponding statement to reorganize the p0sp0 and p0sp1 level-2 partitions under the p0 level-1 partition of the r_l_ntp_t1 table into the p0sp4 and p0sp5 level-2 partitions. The p0sp4 and p0sp5 level-2 partitions are defined by using the following clauses:
(
SUBPARTITION p0sp4 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION p0sp5 VALUES IN (2025,2026,2027,2028,2029)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a table based on the table name.
ALTER TABLE r_l_ntp_t1 /*Table name*/
REORGANIZE SUBPARTITION p0sp0,p0sp1 INTO
(
SUBPARTITION p0sp4 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION p0sp5 VALUES IN (2025,2026,2027,2028,2029)
);Reorganize partitions in a table group
Execute the following statements to reorganize partitions of both the r_l_ntp_t1 and r_l_ntp_t2 tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE SUBPARTITION p0sp0,p0sp1 INTO
(
SUBPARTITION p0sp4 VALUES IN (2020,2021,2022,2023,2024),
SUBPARTITION p0sp5 VALUES IN (2025,2026,2027,2028,2029)
);
## Reorganize partitions of tables in the same table group based on a table name. A table group is automatically found based on the table name you specified and the corresponding partitions in the table group are reorganized.
ALTER TABLEGROUP BY TABLE r_l_ntp_t1 /*Table name*/
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 multiple RANGE level-1 partitions of a specified global index table
In this example, the r_gr_t1 table contains a global index table named g_r. You can execute the following SQL statement to create the r_gr_t1 table:
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));You can execute the corresponding SQL statement to reorganize the p0, p1, and p2 level-1 partitions of the g_r global index table of the r_gr_t1 table into the p4 and p5 level-1 partitions. The p4 and p5 level-1 partitions are defined by using the following clauses:
(
PARTITION sp4 VALUES LESS THAN (15000),
PARTITION sp5 VALUES LESS THAN (30000)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a global index table based on `Table name`.`Global index name`.
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)
);
## Reorganize partitions of a global index table based on `Global index name` ON TALBE `Table name`.
ALTER INDEX `g_r`/*Global index name*/ ON TABLE `r_gr_t1` /*Table name*/
REORGANIZE PARTITION p0,p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (15000),
PARTITION p5 VALUES LESS THAN (30000)
);
Reorganize partitions in a table group
Execute the following statements to reorganize partitions in the tg1 table group:
## Reorganize partitions in a table group based on the table group name. In this example, the name of the table group to which the global index tables belong is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE PARTITION p0,p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (15000),
PARTITION p5 VALUES LESS THAN (30000)
);
## Reorganize partitions of global index tables in the same table group based on `Global index name` ON TABLE `Table name`.
ALTER TABLEGROUP BY INDEX `g_r`/*Global index name*/ ON TABLE `r_gr_t1` /*Table name*/
REORGANIZE PARTITION p0,p1,p2 INTO
(
PARTITION p4 VALUES LESS THAN (15000),
PARTITION p5 VALUES LESS THAN (30000)
);Example 2: Reorganize multiple LIST level-2 partitions of a specified global index table
In this example, the partitioned table r_g_kl_t1 contains a global index table named g_kl. The KEY-LIST global index table g_kl contains level-2 partitions. You can execute the following SQL statement to create the r_g_kl_t1 table:
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));You can execute the corresponding SQL statement to reorganize the sp1 and sp2 level-2 partitions of the g_k_l global index table of the r_g_kl_t1 table into the sp4 and sp5 level-2 partitions. The sp4 and sp5 level-2 partitions are defined by using the following clauses:
(
SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
)The following section describes the statements that are used to reorganize partitions:
Reorganize partitions of a table
## Reorganize partitions of a global index table based on `Table name`.`Global 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)
);
## Reorganize partitions of a global index table based on `Global index name` ON TALBE `Table name`.
ALTER INDEX g_kl/*Global index name*/ ON TABLE `r_g_kl_t1` /*Table name*/
REORGANIZE SUBPARTITION sp1,sp2 INTO
(
SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
);Reorganize partitions in a table group
Reorganize partitions of all logical tables in the same table group. In this example, you can execute the following statements to reorganize partitions of all global index tables in the tg1 table group:
## Reorganize partitions in a table group based on the table group name. In this example, the name of the table group to which the global index tables belong is tg1.
ALTER TABLEGROUP tg1 /*Table group name*/
REORGANIZE SUBPARTITION sp1,sp2 INTO
(
SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
);
## Reorganize partitions of all global index tables in the same table group based on INDEX `Global index name` ON TALBE `Table name`.
ALTER TABLEGROUP BY INDEX `g_kl`/*Global index name*/ ON TABLE `r_g_kl_t1` /*Table name*/
REORGANIZE SUBPARTITION sp1,sp2 INTO
(
SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
);