Split partitions-PolarDB(PolarDB)-阿里云帮助中心

Use ALTER TABLE or ALTER TABLEGROUP to split an existing partition into multiple new partitions. PolarDB-X redistributes data from the original partition into the new partitions without data loss.

Prerequisites

Before you begin, ensure that you have:

PolarDB-X instance version 5.4.14-16539836 or later to split a level-1 partition
PolarDB-X instance version 5.4.17-16952556 or later to split a level-2 partition

Key concepts

Table group — a collection of logical tables or global index tables that share identical partition key columns. When you split a partition in a table group, the corresponding partitions of all tables in the group are split simultaneously.

Global index — an index on a partitioned table built on non-partition key columns. Global indexes can enforce unique constraints.

Templated level-2 partitions — all level-1 partitions share the same level-2 partition definition. Splitting one templated subpartition splits the corresponding subpartition under every level-1 partition.

Non-templated level-2 partitions — each level-1 partition has its own level-2 partition definition. Splitting a non-templated subpartition affects only the specific level-1 partition it belongs to.

Usage notes

After splitting, the new partition definitions must satisfy the data constraints of the original partition. If the new definitions exclude values that exist in the original partition, those rows cannot be placed into any new partition and the split fails.

For non-templated level-2 partitions, you can redefine the level-2 partition definitions when splitting a level-1 partition. For templated level-2 partitions, the level-2 partition definitions cannot be modified when splitting a level-1 partition.

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]

ALTER TABLEGROUP BY TABLE tbl_name automatically identifies the table group that contains the specified table and applies the operation to all tables in that group. The result is identical to ALTER TABLEGROUP tg_name.

Scenario 1: Split a level-1 partition without level-2 partitions

Split a RANGE or RANGE COLUMNS partition

Split a partition of a table

Given the following table:

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

Split p1 into three 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 RANGE partitions, PolarDB-X provides a shorthand syntax to split a partition into exactly two partitions at a boundary value:

-- Equivalent to the full INTO clause below
ALTER TABLE tb1 SPLIT PARTITION p1 AT(9) INTO (PARTITION p11, PARTITION p12);

-- Full equivalent form:
ALTER TABLE tb1 SPLIT PARTITION p1 INTO
  (PARTITION p11 VALUES LESS THAN(9),
   PARTITION p12 VALUES LESS THAN(20));

To verify the result:

SHOW CREATE TABLE tb1\G

Split a partition of a table group

Splitting a partition in a table group splits the corresponding partition in all tables within the group simultaneously.

Given tb1 and tb2 in table group mytg1:

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;

Split p1 in the table group — this splits p1 in both tb1 and tb2:

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

The shorthand AT syntax also works for table groups:

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

Split a LIST or LIST COLUMNS partition

Split a partition of a table

Given the following table:

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

Split p1 into three partitions by redistributing its values:

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

p3 is the default partition — it holds all rows where a is not 1–9. PolarDB-X supports splitting the default partition. Rows with the new listed values are migrated to the specified new partitions; remaining rows stay in the new default partition:

-- Migrates values 10–15 out of the default partition;
-- all other values remain in p32 (default).
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

Splitting a partition in a table group splits the corresponding partition in all tables within the group simultaneously.

Given tb1 and tb2 with the same LIST partition definition in table group mytg1:

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;

Split p1 in the table group — splits p1 in both tb1 and tb2:

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

Split the default partition p3 in the table group:

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

Given the following table, where partitions are named p1, p2, and p3 by default:

CREATE TABLE tb1(a int) PARTITION BY KEY(a) PARTITIONS 3;

Split p1 into two partitions (default behavior):

ALTER TABLE tb1 SPLIT PARTITION p1;

Split p2 into five partitions:

ALTER TABLE tb1 SPLIT PARTITION p2 INTO PARTITIONS 5;

PolarDB-X splits HASH/KEY partitions by dividing the original partition's hash value range.

Split a partition of a table group

Splitting a partition in a table group splits the corresponding partition in all tables within the group simultaneously.

Given tb1 and tb2 in table group mytg1, each with three KEY partitions:

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;

Split p1 into two partitions across both tables:

ALTER TABLEGROUP mytg1 SPLIT PARTITION p1;

Split p2 into five partitions across both tables:

ALTER TABLEGROUP mytg1 SPLIT PARTITION p2 INTO PARTITIONS 5;

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

The same RANGE, LIST, and HASH/KEY split syntax from Scenario 1 applies. The additional consideration is whether to redefine the level-2 partition structure.

Split a partition of a table

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

Option 1: Split without redefining level-2 partitions (inherits the original structure):

ALTER TABLE t1 SPLIT PARTITION p1;

Option 2: Split and redefine the level-2 partitions for each new 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)
  )
);

This splits p1 into two level-1 partitions: p10 with three level-2 partitions and p11 with one level-2 partition.

Important

The new level-2 partition definitions must satisfy the data constraints of the original partition. If not, the split fails and original data cannot be placed into the new partitions.

For templated level-2 partitions, you cannot modify the level-2 partition definitions when splitting a level-1 partition.

Split a partition of a table group

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name. The same level-2 partition rules apply.

Scenario 3: Split a templated level-2 partition

Splitting a templated subpartition applies to the corresponding subpartition under all level-1 partitions.

Split a templated RANGE or RANGE COLUMNS level-2 partition

Split a partition of a table

Given the following table with templated RANGE subpartitions:

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

Split sp2 into three subpartitions across all level-1 partitions:

ALTER TABLE tb1 SPLIT SUBPARTITION sp2 INTO
  (SUBPARTITION sp20 VALUES LESS THAN (1200),
   SUBPARTITION sp21 VALUES LESS THAN (1600),
   SUBPARTITION sp22 VALUES LESS THAN (2000));

The AT shorthand also works for subpartitions:

-- Equivalent to the full INTO clause below
ALTER TABLE tb1 SPLIT SUBPARTITION sp2 AT(1600) INTO
  (SUBPARTITION sp21, SUBPARTITION sp22);

-- Full equivalent form:
ALTER TABLE tb1 SPLIT SUBPARTITION sp2 INTO
  (SUBPARTITION sp21 VALUES LESS THAN (1600),
   SUBPARTITION sp22 VALUES LESS THAN (2000));

Split a partition of a table group

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

Split a templated LIST or LIST COLUMNS level-2 partition

Split a partition of a table

Given the following table with templated LIST subpartitions:

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

Split sp1 into three subpartitions:

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

sp2 is the default subpartition — it holds all rows where c is not one of the four listed dates. Split it to migrate specific date values out of the default:

-- Migrates '2023-01-01' to sp20 and '2023-02-01', '2023-03-01' to sp21;
-- all other dates remain in sp22 (default).
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

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

Split a templated HASH or KEY level-2 partition

Split a partition of a table

Given the following table with templated KEY subpartitions — level-1 partitions are named p1, p2, p3 and templated subpartitions are named sp1, sp2, sp3 by default:

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;

Split sp1 into two subpartitions across all level-1 partitions:

ALTER TABLE tb1 SPLIT SUBPARTITION sp1;

Split sp2 into five subpartitions:

ALTER TABLE tb1 SPLIT SUBPARTITION sp2 INTO SUBPARTITIONS 5;

Split a partition of a table group

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

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

Splitting a non-templated subpartition affects only that subpartition under the specified level-1 partition. The corresponding subpartitions under other level-1 partitions are not affected.

The syntax is identical to Scenario 3. Replace SUBPARTITION references with the specific non-templated subpartition name under the target level-1 partition.