PolarDB:Empty a partition

Usage notes

• If you want to empty 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 empty a level-2 partition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.

• You cannot empty a partition of a global index table.

• You cannot empty a partition of a partitioned table that contains global indexes.

• If you want to empty a partition of a table group, make sure that 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 truncate_partition_operation_definition

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

truncate_partition_operation_definition:
		TRUNCATE PARTITION part_name
 | TRUNCATE SUBPARTITION subpart_name

Scenario 1: Empty a level-1 partition that does not contain level-2 partitions

For example, you can execute the following SQL statements to create two RANGE partitioned tables named r_t1 and r_t2:

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

You can execute one of the following statements to empty the p1 partition of the r_t1 table.

Empty a partition of a table

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

Empty a partition of a table group

When you empty a partition of a table group, the corresponding partitions of all logical tables in the table group are emptied. In this example, you can execute one of the following statements to empty the p1 partition of both the r_t1 and r_t2 tables:

## Empty a partition of a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	TRUNCATE PARTITION p1;;

## Empty a partition of a table group based on a table name. Note that the table group does not contain GSI tables or partitioned tables that contain GSIs.
ALTER TABLEGROUP BY TABLE r_t1 /*Table name*/ 
	TRUNCATE PARTITION p1;

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

When you empty a level-1 partition that contains level-2 partitions, all level-2 partitions under the level-1 partition are also emptied.

For example, you can execute the following SQL statements to create two RANGE-KEY partitioned tables named r_k_tp_t1 and r_k_tp_t2:

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

You can execute one of the following statements to empty the p1 partition of the r_k_tp_t1 table.

Empty a partition of a table

## Empty a partition of a table based on the table name.
ALTER TABLE r_k_tp_t1 /*Table name*/ 
	TRUNCATE PARTITION p1. /*Data in all level-2 partitions under the p1 partition will be emptied.*/

Empty a partition of a table group

When you empty a partition of a table group, the corresponding partitions of all logical tables in the table group are emptied. In this example, you can execute one of the following statements to empty the p1 partition of both the r_k_tp_t1 and r_k_tp_t2 tables:

## Empty a partition of a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	TRUNCATE PARTITION p1;;

## Empty a partition of a table group based on a table name. Note that the table group does not contain GSI tables or partitioned tables that contain GSIs.
ALTER TABLEGROUP BY TABLE r_k_tp_t1 /*Table name*/ 
	TRUNCATE PARTITION p1;

Scenario 3: Empty a templated level-2 partition

When you empty a templated level-2 partition, the corresponding templated level-2 partitions under all level-1 partitions are emptied.

For example, you can execute the following SQL statements to create two KEY-RANGE partitioned tables named k_r_tp_t1 and k_r_tp_t2 that contain templated level-2 partitions:

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

You can execute one of the following statements to empty the sp1 partition of the k_r_tp_t1 table. The sp1 partition is a templated RANGE level-2 partition.

Empty a partition of a table

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

Empty a partition of a table group

When you empty a partition of a table group, the corresponding partitions of all logical tables in the table group are emptied. In this example, you can execute one of the following statements to empty the sp1 partition of both the k_r_tp_t1 and k_r_tp_t2 tables:

## Empty a partition of a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	TRUNCATE SUBPARTITION sp1;

## Empty a partition of a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY TABLE k_r_tp_t1 /*Table name*/ 
	TRUNCATE SUBPARTITION sp1;

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

For non-templated level-2 partitions, you are allowed to empty a non-templated level-2 partition under a specific level-1 partition.

For example, you can execute the following SQL statements to create two LIST-RANGE partitioned tables named l_r_ntp_t1 and l_r_ntp_t2 that contain non-templated level-2 partitions:

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

You can execute one of the following statements to empty the p1sp2 partition under the p1 level-1 partition of the l_r_ntp_t1 table. The p1sp2 partition is a RANGE level-2 partition.

Empty a partition of a table

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

Empty a partition of a table group

When you empty a partition of a table group, the corresponding partitions of all logical tables in the table group are emptied. In this example, you can execute one of the following statements to empty the p1sp2 partition of both the l_r_ntp_t1 and l_r_ntp_t2 tables:

## Empty a partition of a table group based on the table group name.
ALTER TABLEGROUP tg1 /*Table group name*/ 
	TRUNCATE SUBPARTITION p1sp2;

## Empty a partition of a table group based on a table name. The system automatically finds the table group based on the table name.
ALTER TABLEGROUP BY l_r_ntp_t1 /*Table name*/ 
	TRUNCATE SUBPARTITION p1sp2;