Truncate a partition

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-compatible mode of OceanBase Database.

Instructions for truncating a partition

For tables that use RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partitioning (including both partitions and subpartitions) and support the TRUNCATE partition operation, the system does not rebuild global indexes. Instead, it maintains index validity via a lazy (deferred) maintenance strategy, merging and deleting data in the background. This approach prevents query disruptions or performance degradation caused by index invalidation. Note that this optimization only applies to regular global indexes. The following scenarios do not trigger this optimization:

• The partition is a HASH partition (even if the subpartition is a RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partition), and a truncate operation is performed on the subpartition.
• The partitioning key is an expression rather than a regular column.
• For special global indexes such as full-text indexes or those on unstructured types, the index will become unavailable.

Truncate a partition

You can truncate a partition from a table.

The SQL syntax for truncating a partition is as follows:

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

Here are some 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

The SQL syntax for truncating a subpartition is as follows:

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

Here is an example of creating a RANGE-RANGE-subpartitioned table named t_f_rr and clearing 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