Truncate a partition|V4.1.0| docs|Distributed Database

Truncate a partition

Last Updated：2023-07-28 02:55:42 Updated

You can clear the data in a partition of a partitioned table but retain the partition structure.

Support for truncating a partition from a partitioned table

The following table describes the support for truncating partitions from partitioned and subpartitioned tables in MySQL mode of OceanBase Database.

Partitioned table	Partitioning types	Truncate a partition	Truncate a subpartition
Partitioned table	RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS	Supported	-
Partitioned table	HASH and KEY	Unsupported	-
Subpartitioned table	RANGE-RANGE, RANGE-RANGE COLUMNS, RANGE-LIST, and RANGE-LIST COLUMNS	Supported	Supported
Subpartitioned table	RANGE-HASH and RANGE-KEY	Supported	Unsupported
Subpartitioned table	RANGE COLUMNS-RANGE, RANGE COLUMNS-RANGE COLUMNS, RANGE COLUMNS-LIST, and RANGE COLUMNS-LIST COLUMNS	Supported	Supported
Subpartitioned table	RANGE COLUMNS-HASH and RANGE COLUMNS-KEY	Supported	Unsupported
Subpartitioned table	LIST-RANGE, LIST-RANGE COLUMNS, LIST-LIST, and LIST-LIST COLUMNS	Supported	Supported
Subpartitioned table	LIST-HASH and LIST-KEY	Supported	Unsupported
Subpartitioned table	LIST COLUMNS-RANGE, LIST COLUMNS-RANGE COLUMNS, LIST COLUMNS-LIST, and LIST COLUMNS-LIST COLUMNS	Supported	Supported
Subpartitioned table	LIST COLUMNS-HASH and LIST COLUMNS-KEY	Supported	Unsupported
Subpartitioned table	HASH-RANGE, HASH-RANGE COLUMNS, HASH-LIST, HASH-LIST COLUMNS, HASH-HASH, and HASH-KEY	Unsupported	Unsupported
Subpartitioned table	KEY-RANGE, KEY-RANGE COLUMNS, KEY-LIST, KEY-LIST COLUMNS, KEY-HASH, and KEY-KEY	Unsupported	Unsupported

Truncate a partition

You can truncate a partition from a table.

SQL syntax for truncating a partition:

ALTER TABLE table_name TRUNCATE PARTITION partition_name[, partition_name ...];

Note

You can clear the data in one or more partitions by using the TRUNCATE statement.
Before you truncate a partition, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur. You can query the oceanbase.GV$OB_TRANSACTION_PARTICIPANTS view for the context status of unfinished transactions in the sys tenant.
If you truncate a partition for a subpartitioned table, all data in this partition and its subpartitions is cleared.

Examples:

Clear the data in the M202001 and M202002 partitions of the tbl1_r partitioned table.

obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
       PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
        (PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
       , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
       , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
       , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
       , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
       , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
       , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
       , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
       , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
       , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
       , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
       , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
        );
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1_r TRUNCATE PARTITION M202001,M202002;
Query OK, 0 rows affected

Clear the data in the p0 partition of the RANGE COLUMNS-LIST COLUMNS-subpartitioned table t_f_rclc.

Truncate a subpartition

SQL syntax for truncating a subpartition:

ALTER TABLE table_name TRUNCATE SUBPARTITION subpartition_name[, subpartition_name ...];

Note

Before you truncate a subpartition from a subpartitioned table, ensure that no active transactions or queries exist in this subpartition. Otherwise, SQL statement errors or exceptions may occur. You can query the oceanbase.GV$OB_TRANSACTION_PARTICIPANTS view for the context status of unfinished transactions in the sys tenant.
If you want to truncate multiple subpartitions, these partitions must belong to the same partition.

Create a RANGE-RANGE-subpartitioned table named t_f_rr and clear the data in the sp1 and sp2 subpartitions.

obclient> CREATE TABLE t_f_rr(col1 INT,col2 TIMESTAMP)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
           ),
         PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
            )
         );
Query OK, 0 rows affected

obclient> ALTER TABLE t_f_rr TRUNCATE SUBPARTITION sp1,sp2;
Query OK, 0 rows affected