PolarDB:Split a partition into multiple partitions

Usage notes

• If you want to split a level-1 partition, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.

• If you want to split a level-2 partition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.

• After you split a partition into multiple partitions, the new partitions must meet the data constraints of the original partition when you specify the definitions of new partitions. Otherwise, the original data cannot be written to a new partition.

Terms

• Table group: a collection of logical tables or global index tables that share identical partition key columns.

• Global index: 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 { TABLE tbl_name | TABLEGROUP tg_name | TABLEGROUP BY TABLE tbl_name } 
		split_partition_specs_definition
 | split_subpartition_specs_definition

 
split_partition_specs_definition:
SPLIT PARTITION part_name [INTO (partition_specs_definition)]
| SPLIT PARTITION part_name into partitions partitions_count
| SPLIT PARTITION part_name at(#value) into (partition part_name, partition part_name)

split_subpartition_specs_definition:
SPLIT SUBPARTITION subpartition_name [INTO (subpartition_specs_definition)]
| SPLIT SUBPARTITION subpartition_name into subpartitions subpartitions_count
| SPLIT SUBPARTITION subpartition_name at(#value) into (subpartition subpartition_name, subpartition subpartition_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: Split a level-1 partition that does not contain level-2 partitions

Split a RANGE or RANGE COLUMN partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20), 
 PARTITION p2 VALUES LESS THAN(100))

You can execute the following SQL statement to split the p1 partition into the p10, p11, and p12 partitions:

ALTER TABLE tb1 SPLIT PARTITION p1 INTO 
(PARTITION p10 VALUES LESS THAN (8),
PARTITION p11 VALUES LESS THAN(15),
PARTITION p12 VALUES LESS THAN(20))

For tables that are partitioned by using the RANGE partitioning method, PolarDB-X provides a syntactic sugar for you to split a partition into two partitions based on a range. For example, you can execute the following statement to split the p1 partition of the tb1 table:

ALTER TABLE tb1 SPLIT PARTITION p1 AT(9) INTO (partition p11, partition p12)

The result of the preceding SQL statement is the same as the result of the following statement:

ALTER TABLE tb1 SPLIT PARTITION p1 INTO 
(PARTITION p11 VALUES LESS THAN(9),
PARTITION p12 VALUES LESS THAN(20));

Split a partition of a table group

For example, the partition definition of the tb1 table is the same as that of the tb2 table, and the two tables are in the mytg1 table group. In this example, the table group must be unique and mytg1 is used.

create tablegroup mytg1;
CREATE TABLE tb1(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20), 
 PARTITION p2 VALUES LESS THAN(100)) tablegroup=mytg1;
 
CREATE TABLE tb2(a int) PARTITION BY RANGE(a)
 (PARTITION p1 VALUES LESS THAN(20), 
 PARTITION p2 VALUES LESS THAN(100)) tablegroup=mytg1; 

You can execute the following SQL statement to split the p1 partition of the mytg1 table group into the p10, p11, and p12 partitions. This indicates that both the p1 partition of tb1 and the p1 partition of tb2 are split into the p10, p11, and p12 partitions.

ALTER TABLEGROUP mytg1 SPLIT PARTITION p1 INTO 
(PARTITION p10 VALUES LESS THAN (8),
PARTITION p11 VALUES LESS THAN(15),
PARTITION p12 VALUES LESS THAN(20))

For table groups whose tables are partitioned by using the RANGE partitioning method, PolarDB-X provides a syntactic sugar for you to split a partition into two partitions based on a range. For example, you can execute the following statement to split the p1 partition of the mytg1 table group:

ALTER TABLEGROUP mytg1 SPLIT PARTITION p1 AT(9) INTO (partition p11, partition p12)

The result of the preceding SQL statement is the same as the result of the following statement:

ALTER TABLEGROUP mytg1 SPLIT PARTITION p1 INTO 
(PARTITION p11 VALUES LESS THAN(9),
PARTITION p12 VALUES LESS THAN(20));

Split a LIST or LIST COLUMN partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1(a int) PARTITION BY LIST(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6), 
 PARTITION p2 VALUES IN(7,8,9),
 PARTITION p3 VALUES IN(default))

You can execute the following SQL statement to split the p1 partition into the p10, p11, and p12 partitions:

ALTER TABLE tb1 SPLIT PARTITION p1 INTO 
(PARTITION p10 VALUES IN (1,3,5),
PARTITION p11 VALUES IN (2,4),
PARTITION p12 VALUES IN (6))

The p3 partition is the default partition that captures any rows in which the partition key values are not 1 to 9. PolarDB-X allows you to split the default partition. If you split the default partition, some values in the default partition are migrated to new partitions. For example, you can execute the following SQL statement to split the p3 partition into three partitions named p30, p31, and p32, and migrate values 10 to 15 to the p30 and p31 partitions:

ALTER TABLE tb1 SPLIT PARTITION p3 INTO 
(PARTITION p30 VALUES IN (10,11,12),
PARTITION p31 VALUES IN (13,14,15),
PARTITION p32 VALUES IN (default))

Split a partition of a table group

For example, the partition definition of the tb1 table is the same as that of the tb2 table, and the two tables are in the mytg1 table group. In this example, the table group must be unique and mytg1 is used.

create tablegroup mytg1;
CREATE TABLE tb1(a int) PARTITION BY LIST(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6), 
 PARTITION p2 VALUES IN(7,8,9),
 PARTITION p3 VALUES IN(default)) tablegroup=mytg1;
 
CREATE TABLE tb2(a int) PARTITION BY LIST(a)
 (PARTITION p1 VALUES IN(1, 2, 3, 4, 5, 6), 
 PARTITION p2 VALUES IN(7,8,9),
 PARTITION p3 VALUES IN(default)) tablegroup=mytg1; 

You can execute the following SQL statement to split the p1 partition into the p10, p11, and p12 partitions. This indicates that both the p1 partition of tb1 and the p1 partition of tb2 are split into the p10, p11, and p12 partitions.

ALTER TABLEGROUP mytg1 SPLIT PARTITION p1 INTO 
(PARTITION p10 VALUES IN (1,3,5),
PARTITION p11 VALUES IN (2,4),
PARTITION p12 VALUES IN (6))

The p3 partition is the default partition that captures any rows in which the partition key values are not 1 to 9. PolarDB-X allows you to split the default partition. If you split the default partition, some values in the default partition are migrated to new partitions. For example, you can execute the following SQL statement to split the p3 partition into three partitions named p30, p31, and p32, and migrate values 10 to 15 to the p30 and p31 partitions:

ALTER TABLEGROUP mytg1 SPLIT PARTITION p3 INTO 
(PARTITION p30 VALUES IN (10,11,12),
PARTITION p31 VALUES IN (13,14,15),
PARTITION p32 VALUES IN (default))

Split a HASH or KEY partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1(a int) PARTITION BY key(a) partitions 3;

By default, the three partitions are named p1, p2, and p3.

You can execute the following SQL statement to split the p1 partition into two partitions based on the range of hash values of the original p1 partition:

ALTER TABLE tb1 split partition p1

You can also split a partition into a specified number of partitions. For example, you can execute the following SQL statement to split the p2 partition into five partitions based on the range of hash values of the original p2 partition:

ALTER TABLE tb1 split partition p2 INTO partitions 5

Split a partition of a table group

For example, the partition definition of the tb1 table is the same as that of the tb2 table, and the two tables are in the mytg1 table group. In this example, the table group must be unique and mytg1 is used.

CREATE tablegroup mytg1;
CREATE TABLE tb1(a int) PARTITION BY key(a) partitions 3 tablegroup=mytg1;
CREATE TABLE tb2(a int) PARTITION BY key(a) partitions 3 tablegroup=mytg1;

By default, the three partitions of both the tb1 and tb2 tables are named p1, p2, and p3.

You can execute the following SQL statement to split the p1 partition of the mytg1 table group into two partitions based on the range of hash values of the original p1 partition:

ALTER tablegroup mytg1 split partition p1

You can also split a partition into a specified number of partitions. For example, you can execute the following SQL statement to split the p2 partition into five partitions based on the range of hash values of the original p2 partition:

ALTER tablegroup mytg1 split partition p2 into partitions 5

Scenario 2: Split a level-1 partition that contains level-2 partitions

The partitioning methods used in scenario 1 can also be used in scenario 2.

Split a partition of a table

For non-templated level-2 partitions, you can redefine the level-2 partitions when you split level-1 partitions. The following statement is used to create a table with non-templated level-2 partitions:

CREATE TABLE t1 (
a bigint unsigned NOT null,
b bigint unsigned NOT null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition BY key (a,b) partitions 4
subpartition BY range columns (c,d)
(
partition p1
(
subpartition p1sp1 VALUES less than ( '2020-01-01', 'abc' ),
subpartition p1sp2 VALUES less than ( maxvalue, maxvalue )
),
partition p2
(
subpartition p2sp1 VALUES less than ( '2020-01-01', 'abc' ),
subpartition p2sp2 VALUES less than ( '2021-01-01', 'abc' ),
subpartition p2sp3 VALUES less than ( '2022-01-01', 'abc' ),
subpartition p2sp4 VALUES less than ( maxvalue, maxvalue )
),
partition p3
(
subpartition p3sp1 VALUES less than ( '2020-01-01', 'abc' ),
subpartition p3sp2 VALUES less than ( maxvalue, maxvalue )
),
partition p4
(
subpartition p4sp1 VALUES less than ( '2020-01-01', 'abc' ),
subpartition p4sp2 VALUES less than ( maxvalue, maxvalue )
)
);

You can execute the following SQL statement to split a level-1 partition in the same way that you did in scenario 1:

ALTER TABLE t1 split partition p1;

You can redefine the level-2 partitions when you split the level-1 partition.

ALTER TABLE t1 split partition p1 INTO (
 partition p10 (subpartition p10sp1 VALUES less than('2020-01-01', 'abc'),
 subpartition p10sp2 VALUES less than('2022-01-01', 'abc'),
 subpartition p10sp3 VALUES less than(maxvalue, maxvalue)),
 partition p11 (subpartition p11sp1 VALUES less than(maxvalue, maxvalue)));

The preceding statement is used to split the p1 partition into two level-1 partitions named p10 and p11, and specify that the p10 partition contains three level-2 partitions and the p11 partition contains one level-2 partition.

For templated level-2 partitions, you are not allowed to modify the definitions of the level-2 partitions when you split level-1 partitions.

Split a partition of a table group

You can split a partition of a table group in a similar way that you did for a table. You need to only change ALTER TABLE #tb to ALTER TABLEGROUP #tgname or ALTER TABLEGROUP BY #tb.

Scenario 3: Split a templated level-2 partition

You can execute an SQL statement to split a templated level-2 partition. The corresponding templated level-2 partitions under all level-1 partitions are split.

Split a templated RANGE or RANGE COLUMN level-2 partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1 (
a bigint unsigned NOT null,
b bigint unsigned NOT null,
c bigint NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition BY key (a,b) partitions 4
subpartition BY range (c) (
 subpartition sp1 VALUES less than ( 1000 ),
 subpartition sp2 VALUES less than ( 2000 ),
 subpartition sp3 VALUES less than ( maxvalue )
);

You can execute the following SQL statement to split the sp2 partition into the sp20, sp21, and sp22 partitions:

ALTER TABLE tb1 split subpartition sp2 INTO 
(partition sp20 VALUES less than (1200),
partition sp21 VALUES less than(1600),
partition sp22 VALUES less than(2000))

For tables that are partitioned by using the RANGE partitioning method, PolarDB-X provides a syntactic sugar for you to split a partition into two partitions based on a range. For example, you can execute the following SQL statement to split the sp2 partition of the tb1 table:

ALTER TABLE tb1 split subpartition sp2 at(1600) INTO
(subpartition sp21, subpartition p22)

The result of the preceding SQL statement is the same as the result of the following statement:

ALTER TABLE tb1 split subpartition sp2 INTO
(partition sp21 VALUES less than(1600),
partition sp22 VALUES less than(2000));

Split a partition of a table group

You can split a partition of a table group in a similar way that you did for a table. You need to only change ALTER TABLE #tb to ALTER TABLEGROUP #tgname or ALTER TABLEGROUP BY #tb.

Split a templated LIST or LIST COLUMN level-2 partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1 (
a bigint unsigned NOT null,
b bigint unsigned NOT null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition BY key (a,b) partitions 4
subpartition BY list (to_days(c)) (
subpartition sp1 VALUES IN ( to_days('2020-01-01'),to_days('2020-02-01'),to_days('2020-03-01'),to_days('2020-04-01') ),
subpartition sp2 VALUES IN ( default )
);

You can execute the following SQL statement to split the sp1 partition into the sp10, sp11, and sp12 partitions:

ALTER TABLE tb1 split subpartition sp1 INTO 
(subpartition sp10 VALUES IN (to_days('2020-01-01')),
subpartition sp11 VALUES IN (to_days('2020-02-01'),to_days('2020-04-01')),
subpartition sp12 VALUES IN (to_days('2020-03-01')))

The sp2 partition is the default partition that captures any rows in which the values of the c column are not '2020-01-01', '2020-02-01', '2020-03-01', or '2020-04-01'. The c column is the partition key column. PolarDB-X allows you to split the default partition. If you split the default partition, some values in the default partition are migrated to new partitions. For example, you can execute the following SQL statement to split the sp2 partition into three partitions named sp20, sp21, and sp22, and migrate the value '2023-01-01' to the sp20 partition and the values '2023-02-01' and '2023-03-01' to the sp21 partition:

ALTER TABLE tb1 split subpartition sp2 INTO 
(subpartition sp20 VALUES IN (to_days('2023-01-01')),
subpartition sp21 VALUES IN (to_days('2023-02-01'),to_days('2023-03-01')),
subpartition sp22 VALUES IN (default))

Split a partition of a table group

You can split a partition of a table group in a similar way that you did for a table. You need to only change ALTER TABLE #tb to ALTER TABLEGROUP #tgname or ALTER TABLEGROUP BY #tb.

Split a templated HASH or KEY level-2 partition

Split a partition of a table

For example, the tale is tb1, which is created and defined by using the following SQL statement:

CREATE TABLE tb1 (
a bigint unsigned NOT null,
b bigint unsigned NOT null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition BY key (c,d) partitions 3
subpartition BY key (a,b) subpartitions 3;

By default, the level-1 partitions are named p1, p2, and p3, and the templated level-2 partitions are named sp1, sp2, and sp3.

You can execute the following SQL statement to split the sp1 partition into two partitions based on the range of hash values of the original sp1 partition:

ALTER TABLE tb1 split subpartition sp1

You can also split a partition into a specified number of partitions. For example, you can execute the following SQL statement to split the sp2 partition into five partitions based on the range of hash values of the original sp2 partition:

ALTER TABLE tb1 split subpartition sp2 INTO subpartitions 5

Split a partition of a table group

You can split a partition of a table group in a similar way that you did for a table. You need to only change ALTER TABLE #tb to ALTER TABLEGROUP #tgname or ALTER TABLEGROUP BY #tb.

Scenario 4: Split a non-templated level-2 partition

The partitioning methods used in scenario 3 can also be used in scenario 4. For more information about the syntax, see the Scenario 3: Split a templated level-2 partition section of this topic.