重组分区

注意事项

• 本文要求PolarDB-X实例版本必须为5.4.14-16539836及以上；

• 若要对二级分区进行重组操作，要求PolarDB-X实例版本必须为5.4.17-16952556及以上。

• 哈希类型的分区策略，例如HASH/KEY/CO_HASH，不支持执行该操作。

• 重组分区时，被重组的分区集合所组成的边界值范围定义必须与重组后的新分区的边界值范围定义一致。

名词解释

• 表组：分区列完全相同的一组逻辑表或全局索引表的集合。

• 全局索引：使用另一个维度进行水平分区的数据与主表始终保持强一致的分区表。

语法

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) 

# 二级分区的Range/Range Columns分区定义
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] 

# 二级分区的List/List Columns分区定义
list_subpartition_list:
	(list_subpartition [, list_subpartition ...])

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

场景1：目标分区是不含二级分区的一级分区

示例1：重组多个一级RANGE分区

假设r_t1与r_t2都采用Range分区，建表SQL如下所示：

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)
);

给r_t1表的p1、p2、p3这3个连续分区重组为p4、p5,，且p4、p5的定义是( PARTITION p4 VALUES LESS THAN (2024),PARTITION p5 VALUES LESS THAN (2027))。具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
ALTER TABLE r_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2,p3 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2024), 
 PARTITION p5 VALUES LESS THAN (2027) 
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作, 假设r_t1与r_t2的表组名字是tg1
ALTER TABLEGROUP tg1 /*表组名*/ 
		REORGANIZE PARTITION p1,p2,p3 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2024), 
 PARTITION p5 VALUES LESS THAN (2027) 
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
ALTER TABLEGROUP BY TABLE r_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2,p3 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2024), 
 PARTITION p5 VALUES LESS THAN (2027) 
 );

示例2：重组多个一级LIST分区

假设l_t1与l_t2都采用List分区，建表SQL如下所示：

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)
);

将l_t1表的p0、p1分区重组为p3、p4，且它们的定义为( PARTITION p3 VALUES IN (2020,2021,2022,2023,2024), PARTITION p4 VALUES IN (2025,2026,2027,2028,2029))。具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
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)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
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)
 );

场景2：目标分区是含有二级分区的一级分区

示例1：重组多个一级分区, 同时指定二级分区定义

假设r_k_ntp_t1与r_k_ntp_t2都采用Range+Key的非模板化二级分区，建表SQL如下所示：

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
);

将表r_k_ntp_t1的p、p2重组为新的分区p4，p4的定义为PARTITION p2 VALUES LESS THAN (2022) SUBPARTITIONS 4。具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
ALTER TABLE r_k_ntp_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 (
 	PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
ALTER TABLEGROUP tg1 /*表组名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 (
 	PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
ALTER TABLEGROUP BY TABLE r_k_ntp_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 (
 	PARTITION p4 VALUES LESS THAN (2022) SUBPARTITIONS 1
 );

示例2：重组多个一级分区, 但不指定二级分区定义

该种用法与场景1（目标分区是不含二级分区的一级分区）的用法类似。具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
ALTER TABLE r_k_ntp_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2022)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
ALTER TABLEGROUP tg1 /*表组名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2022)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
ALTER TABLEGROUP BY TABLE r_k_ntp_t1 /*表名*/ 
		REORGANIZE PARTITION p1,p2 INTO 
 	( 
 PARTITION p4 VALUES LESS THAN (2022)
 );

场景3：目标分区是模板化的二级分区

示例1：重组多个二级RANGE分区

假设k_r_tp_t1与k_r_tp_t2都采用Key+Range的模板化分区，建表SQL如下所示：

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)
);

给k_r_tp_t1表的二级分区模板的sp1、sp2重组为新sp4、sp5、sp6，且新的分区定义如下：

(
 SUBPARTITION sp4 VALUES LESS THAN (2500),
 SUBPARTITION sp5 VALUES LESS THAN (2800),
 SUBPARTITION sp6 VALUES LESS THAN (3000)
)

具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
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)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
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)
 );

示例2：重组多个二级LIST分区

假设k_l_tp_t1与k_l_tp_t2都采用List分区，建表SQL如下所示：

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)
);

给k_l_tp_t1表的二级分区模板sp0、sp1，重组为sp3、sp4，且sp3、sp4的定义如下：

(
 SUBPARTITION sp3 VALUES IN (2020,2021,2022,2023,2024),
 SUBPARTITION sp4 VALUES IN (2025,2026,2027,2028,2029)
)

具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
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)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
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)
 );

场景4：目标分区是非模板化的二级分区

示例1：重组指定的一级分区的多个二级RANGE分区

假设l_r_ntp_t1与l_r_ntp_t2都采用List+Range的非模板化分区，建表SQL如下所示：

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)
	)
);

给l_r_ntp_t1表的一级分区p0下的二级分区p0sp1、p0sp2重组为p0sp4、p0sp5、p0sp6，新的p0sp4、p0sp5、p0sp6分区的定义为：

(
 SUBPARTITION p0sp4 VALUES LESS THAN (2500),
 SUBPARTITION p0sp5 VALUES LESS THAN (2800),
 SUBPARTITION p0sp6 VALUES LESS THAN (3000)
)

具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
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)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
ALTER TABLEGROUP BY 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)
 );

示例2：重组指定的一级分区的多个二级LIST分区

假设r_l_ntp_t1与r_l_ntp_t2都采用List+Range非模板化二级分区，建表SQL如下所示：

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)
 )
);

给l_r_ntp_t1表的一级分区p0下的二级分区p0sp0、p0sp1重组为p0sp4、p0sp5，新的p0sp4、p0sp5 二级分区的定义为：

(
 SUBPARTITION p0sp4 VALUES IN (2020,2021,2022,2023,2024),
 SUBPARTITION p0sp5 VALUES IN (2025,2026,2027,2028,2029)
)

具体用法如下所示：

表级用法

## 基于表名字进行表级的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作
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)
 );

## 基于表名进行表组级的分区变更操作（即基于表名自动找查对应的表组并进行操作）
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)
 );

场景5：目标分区是全局索引表的分区

示例1：重组指定的全局索引表的多个一级RANGE分区

假设r_gr_t1是一个使用全局索引的表，建表SQL如下所示：

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));

现在给 r_gr_t1 表的全局索引 g_r 的一级分区 p0,p1,p2 重组为 p4,p5, 且 p4,p5 的定义为

(
 PARTITION sp4 VALUES LESS THAN (15000),
 PARTITION sp5 VALUES LESS THAN (30000)
)

具体用法如下所示：

表级用法

## 基于 `表名`.`全局索引名` 进行全局索引表的分区变更操作
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)
 );

## 基于 `全局索引` ON TALBE `表名` 进行全局索引表的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即上述两个表同时执行分区变更：

## 基于表组名字进行表组级的分区变更操作，假如全局索引表的表组名是tg1
ALTER TABLEGROUP tg1 /*表组名*/ 
 REORGANIZE PARTITION p0,p1,p2 INTO 
 (
 PARTITION p4 VALUES LESS THAN (15000),
 PARTITION p5 VALUES LESS THAN (30000)
 );

## 基于 `全局索引` ON TALBE `表名` 进行全局索引表的表组的分区变更操作
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)
 );

示例2：重组指定的全局索引表的多个二级LIST分区

假设r_g_kl_t1是一个使用全局索引的分区表，其中全局索引使用Key+List的二级分区，建表SQL如下所示：

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));

给r_g_kl_t1表的全局索引g_kl的二级分区模板sp1、sp2重组为新的sp4、sp5，且sp4、sp5的定义如下：

(
	SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
 SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
)

具体用法如下所示：

表级用法

## 基于 `表名`.`全局索引名` 进行全局索引表的分区变更操作
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)
 );

## 基于 `全局索引` ON TALBE `表名` 进行全局索引表的分区变更操作
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)
 );

表组级用法

同一个表组的所有逻辑表同时进行增加分区的操作，即修改全局索引所对应的表组的分区定义：

## 基于表组名字进行表组级的分区变更操作，假如全局索引表的表组名是tg1
ALTER TABLEGROUP tg1 /*表组名*/ 
 REORGANIZE SUBPARTITION sp1,sp2 INTO 
 (
 	SUBPARTITION `sp4` VALUES IN (20000,20001,20002),
 SUBPARTITION `sp5` VALUES IN (20003,20004,30000,30001,30002)
 );

## 基于 INDEX `全局索引` ON TALBE `表名` 进行全局索引表的表组的分区变更操作
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)
 );