Empty a partition

Prerequisites

• To empty a level-1 partition, your PolarDB-X instance must be version 5.4.14-16539836 or later.

• To empty a level-2 partition, your PolarDB-X instance must be version 5.4.17-16952556 or later.

Limitations

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

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

• To empty a partition of a table group, the table group must not contain global secondary index (GSI) tables or partitioned tables that contain GSIs.

Key concepts

• Table group: a collection of logical tables or global index tables that share identical partition key columns. When you empty a partition of a table group, the corresponding partition is emptied across all logical tables in the group.

• Global index: an indexing technique for partitioned tables. Global indexes are created on non-partition keys and can enforce 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

Use TRUNCATE PARTITION to empty a level-1 partition. Use TRUNCATE SUBPARTITION to empty a level-2 partition.

The ALTER target supports three forms:

Empty a level-1 partition without subpartitions

The following example uses two RANGE partitioned tables, r_t1 and r_t2, partitioned by YEAR(c).

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

Empty partition `p1` on a single table:

-- Target a table by name
ALTER TABLE r_t1
  TRUNCATE PARTITION p1;

Empty partition `p1` across a table group (affects r_t1 and r_t2):

-- Target a table group by name
ALTER TABLEGROUP tg1
  TRUNCATE PARTITION p1;

-- Target a table group by specifying one of its member tables
ALTER TABLEGROUP BY TABLE r_t1
  TRUNCATE PARTITION p1;

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

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

The following example uses two RANGE-KEY partitioned tables, r_k_tp_t1 and r_k_tp_t2.

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

Empty partition `p1` on a single table (empties all level-2 partitions under p1):

-- Target a table by name
ALTER TABLE r_k_tp_t1
  TRUNCATE PARTITION p1;

Empty partition `p1` across a table group (affects r_k_tp_t1 and r_k_tp_t2, including all their level-2 partitions under p1):

-- Target a table group by name
ALTER TABLEGROUP tg1
  TRUNCATE PARTITION p1;

-- Target a table group by specifying one of its member tables
ALTER TABLEGROUP BY TABLE r_k_tp_t1
  TRUNCATE PARTITION p1;

Empty a templated level-2 partition

Emptying a templated level-2 partition empties that subpartition across all level-1 partitions.

The following example uses two KEY-RANGE partitioned tables, k_r_tp_t1 and k_r_tp_t2, with templated RANGE subpartitions.

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

Empty subpartition `sp1` on a single table (empties sp1 under every level-1 partition of k_r_tp_t1):

-- Target a table by name
ALTER TABLE k_r_tp_t1
  TRUNCATE SUBPARTITION sp1;

Empty subpartition `sp1` across a table group (affects k_r_tp_t1 and k_r_tp_t2):

-- Target a table group by name
ALTER TABLEGROUP tg1
  TRUNCATE SUBPARTITION sp1;

-- Target a table group by specifying one of its member tables
ALTER TABLEGROUP BY TABLE k_r_tp_t1
  TRUNCATE SUBPARTITION sp1;

Empty a non-templated level-2 partition

For non-templated level-2 partitions, each level-1 partition has its own set of subpartitions with independent names. Emptying a subpartition affects only that specific subpartition under its level-1 parent.

The following example uses two LIST-RANGE partitioned tables, l_r_ntp_t1 and l_r_ntp_t2, with non-templated RANGE subpartitions.

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

Empty subpartition `p1sp2` (under level-1 partition `p1`) on a single table:

-- Target a table by name and specify the subpartition name
ALTER TABLE l_r_ntp_t1
  TRUNCATE SUBPARTITION p1sp2;

Empty subpartition `p1sp2` across a table group (affects l_r_ntp_t1 and l_r_ntp_t2):

-- Target a table group by name
ALTER TABLEGROUP tg1
  TRUNCATE SUBPARTITION p1sp2;

-- Target a table group by specifying one of its member tables
ALTER TABLEGROUP BY TABLE l_r_ntp_t1
  TRUNCATE SUBPARTITION p1sp2;