Truncate partition

You can clear the data in a partition of a partitioned table while retaining the partition structure based on your business needs.

Support for Truncate partition operations

In the MySQL mode of OceanBase Database, the support for Truncate partition operations on primary and secondary partitioned tables is as follows:

Description of Truncate partition operations

Impact of Truncate partition operations on global indexes

For V4.3.5, starting from V4.3.5 BP2, for tables that use Range/Range Columns, List/List Columns partitioning (including primary and secondary partitions) and support TRUNCATE partition operations, the system does not rebuild global indexes. Instead, it uses a lazy maintenance strategy to keep the indexes valid by deleting data through background compactions. This avoids query interruptions or performance degradation caused by index invalidation. Note that this optimization applies only to regular global indexes. The following scenarios do not trigger this optimization:

• The primary partition is a Hash partition (even if the secondary partition is Range/Range Columns or List/List Columns). In this case, a Truncate operation is performed on the secondary partition.
• The partitioning key is an expression rather than a regular column.

Hidden tenant-level configuration item to control global index behavior during partition DDL operations

For V4.3.5, OceanBase Database introduces a tenant-level hidden configuration item _ob_enable_truncate_partition_preserve_global_index starting from V4.3.5 BP2. This configuration item controls whether global indexes remain valid during TRUNCATE/DROP operations on primary partitions (referred to as partition DDL operations). For upgraded tenants, the default value is False, and for new tenants, it is True. The behavior is as follows:

Examples:

• Global index remains valid.

  obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = True;
  

• Global index becomes invalid.

  obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = False;
  

Impact of Truncate partition operations on concurrent DDL/DML/queries

Truncate partition operations are offline DDL operations. During a Truncate partition operation, OceanBase Database adds an exclusive lock at the partition level on the target partition.

Truncate a partition

You can truncate a partition of a partitioned 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 data from the M202001 and M202002 partitions of the partitioned table tbl1_r.

  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 data from the p0 partition of the partitioned table t_f_rclc of the RANGE COLUMNS + LIST COLUMNS type.

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 clearing data from the sp1 and sp2 subpartitions of the RANGE + RANGE partitioned table t_f_rr:

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