Truncate partitions

You can truncate partitions in a partitioned table to clear the data in the partitions while retaining the partition structure.

Support for truncating partitions

In the MySQL mode of OceanBase Database, the support for truncating partitions for primary and composite partitioned tables is described in the following table.

Truncate partition

Impact of truncating partitions on global indexes

For tables that use Range/Range Columns or List/List Columns partitioning (including primary and composite partitions) and support the TRUNCATE partition operation, the system does not rebuild global indexes. Instead, it uses a lazy maintenance strategy to keep the indexes valid by background data deletion. This avoids query interruptions or performance degradation due to index invalidation. Note that this optimization applies only to regular global indexes. The following scenarios cannot trigger this optimization:

• The primary partition is a Hash partition (even if the composite partition is Range/Range Columns or List/List Columns), and the Truncate operation is performed on the composite partition.
• The partitioning key is an expression, not a regular column.

Hidden configuration item for controlling global index behavior during partition DDL

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

Examples:

• Indexes remain valid.

  obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = True;
  

• Indexes become invalid.

  obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = False;
  

Impact of truncating partitions on concurrent DDL/DML/queries

Truncating partitions is an offline DDL operation. During this 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 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 t_f_rclc partitioned table, which is partitioned by range columns and list columns.

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 the data in the sp1 and sp2 subpartitions of the t_f_rr partitioned table, which is partitioned by range and range.

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