Delete a partition - PolarDB - Alibaba Cloud Documentation Center

Usage notes

To delete a level-1 partition, the version of your Alibaba Cloud PolarDB for Xscale (PolarDB-X) instance must be 5.4.14-16539836 or later.
To delete a level-2 partition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.
You cannot delete a partition of a table that is partitioned by using the HASH, KEY, or CO_HASH partitioning method.
You cannot delete a partition of a table that is partitioned by using the RANGE or RANGE COLUMNS partitioning method and contains a catch-all partition, MAXVALUE.
You cannot delete a partition of a table that is partitioned by using the LIST or LIST COLUMNS partitioning method and contains a catch-all partition, DEFAULT.
You cannot delete a partition of a global index table.
You cannot delete a partition of a partitioned table that contains global indexes.
You can delete partitions of tables in the same table group only if the table group does not contain global secondary index (GSI) tables or partitioned tables that contain GSIs.

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 alter_target_definition drop_partition_operation_definition

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

drop_partition_operation_definition:
		DROP PARTITION part_name
 | DROP SUBPARTITION subpart_name

Scenario 1: Delete a level-1 partition that does not contain level-2 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 (2021)
);

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

Delete a level-1 partition (p1 in this example) of the r_t1 table. The following section describes the statements that are used to delete partitions:

Delete a partition of a table

## Delete a partition of a table based on the table name.
ALTER TABLE r_t1 /*Table name*/ 
	DROP PARTITION p1;

Delete partitions in a table group

Execute the following statements to delete the p1 partition of both the r_t1 and r_t2 tables in the tg1 table group:

## Delete partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	DROP PARTITION p1;;

## Delete 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 deleted.
ALTER TABLEGROUP BY TABLE r_t1 /*Table name*/ 
	DROP PARTITION p1;

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

If you delete a level-1 partition that contains level-2 partitions, all the level-2 partitions under the level-1 partition are deleted.

In this example, the r_k_tp_t1 and r_k_tp_t2 tables are partitioned by using the RANGE partitioning method. You can execute the following SQL statements to create the tables:

CREATE TABLE `r_k_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 RANGE(YEAR(`c`)) 
SUBPARTITION BY KEY(`a`) SUBPARTITIONS 2
(
 PARTITION p0 VALUES LESS THAN (2020),
 PARTITION p1 VALUES LESS THAN (2021)
);

CREATE TABLE `r_k_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 RANGE(YEAR(`c`)) 
(
 PARTITION p0 VALUES LESS THAN (2020),
 PARTITION p1 VALUES LESS THAN (2021)
);

Delete a level-1 partition (p1 in this example) of the r_k_tp_t1 table. The following section describes the statements that are used to delete partitions:

Delete a partition of a table

## Delete a partition of a table based on the table name.
ALTER TABLE r_k_tp_t1 /*Table name*/ 
	DROP PARTITION p1;

Delete partitions in a table group

Execute the following statements to delete the p1 partition of both the r_k_tp_t1 and r_k_tp_t2 tables in the tg1 table group:

## Delete partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	DROP PARTITION p1;;

## Delete 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 deleted.
ALTER TABLEGROUP BY TABLE r_k_tp_t1 /*Table name*/ 
	DROP PARTITION p1;

Scenario 3: Delete a templated level-2 partition

If you delete a templated level-2 partition, the corresponding level-2 partition under all level-1 partitions will be deleted.

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 statements 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(YEAR(`c`))
(
 SUBPARTITION sp0 VALUES LESS THAN (2020),
 SUBPARTITION sp1 VALUES LESS THAN (2021)
);

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(YEAR(`c`))
(
 SUBPARTITION sp0 VALUES LESS THAN (2020),
 SUBPARTITION sp1 VALUES LESS THAN (2021)
);

Delete a templated RANGE level-2 partition (sp1 in this example) of the k_r_tp_t1 table. The following section describes the statements that are used to delete partitions:

Delete a partition of a table

## Delete a partition of a table based on the table name.
ALTER TABLE k_r_tp_t1 /* Table name*/ 
	DROP SUBPARTITION sp1;

Delete partitions in a table group

Execute the following statements to delete the sp1 level-2 partition of both the k_r_tp_t1 and k_r_tp_t2 tables in the tg1 table group:

## Delete partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	DROP SUBPARTITION sp1;

## Delete 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 deleted.
ALTER TABLEGROUP BY TABLE k_r_tp_t1 /*Table name*/ 
	DROP SUBPARTITION sp1;

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

You can individually delete non-templated level-2 partitions under a single 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 SQL 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(YEAR(`c`))
(
 PARTITION p0 VALUES IN (2020,2022) (
 	SUBPARTITION p0sp0 VALUES LESS THAN (2020),
 	SUBPARTITION p0sp1 VALUES LESS THAN (2022)
	),
 PARTITION p1 VALUES IN (2021,2023) (
 	SUBPARTITION p1sp0 VALUES LESS THAN (2021),
 SUBPARTITION p1sp1 VALUES LESS THAN (2023),
 SUBPARTITION p1sp2 VALUES LESS THAN (2025)
	)
);

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(YEAR(`c`))
(
 PARTITION p0 VALUES IN (2020,2022) (
 	SUBPARTITION p0sp0 VALUES LESS THAN (2020),
 	SUBPARTITION p0sp1 VALUES LESS THAN (2022)
	),
 PARTITION p1 VALUES IN (2021,2023) (
 	SUBPARTITION p1sp0 VALUES LESS THAN (2021),
 SUBPARTITION p1sp1 VALUES LESS THAN (2023),
 SUBPARTITION p1sp2 VALUES LESS THAN (2025)
	)
);

Delete a non-templated RANGE level-2 partition (p1sp2 in this example) under the level-1 partition p1 of the l_r_ntp_t1 table. The following section describes the statements that are used to delete partitions:

Delete a partition of a table

## Delete a partition of a table based on the table name and the name of the level-2 partition that you want to delete.
ALTER TABLE l_r_ntp_t1 /*Table name*/ 
	DROP SUBPARTITION p1sp2;

Delete partitions in a table group

Execute the following statements to delete the p1sp2 level-2 partition of both the l_r_ntp_t1 and l_r_ntp_t2 tables in the tg1 table group:

## Delete partitions in a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	DROP SUBPARTITION p1sp2;

## Delete 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 deleted.
ALTER TABLEGROUP BY l_r_ntp_t1 /*Table name*/ 
	DROP SUBPARTITION p1sp2;