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

Take note of the following points in the Oracle-compatible mode of OceanBase Database:

• For partitioned tables, you can truncate partitions only for RANGE and LIST partitions. You cannot truncate partitions for HASH partitions.

• For subpartitioned tables, you can truncate partitions only for RANGE- or LIST-type (combined) partitions.

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

Considerations

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.

Impact on global indexes when you truncate a partition

In the Oracle-compatible mode of OceanBase Database, when you truncate a partition from a partitioned or subpartitioned table with global indexes, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this partitioned table become unavailable after the partition is truncated.

For tables using RANGE, INTERVAL, or LIST partitions (including both partitions and subpartitions) that support the TRUNCATE partition operation, when UPDATE GLOBAL INDEXES is specified, 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 RANGE or LIST), and a truncate operation is performed on the subpartition.
• The partitioning key is an expression, not a regular column.

Hidden tenant-level configuration item for controlling global index behavior during partition DDL operations

For OceanBase Database V4.3.5, the tenant-level hidden configuration item _ob_enable_truncate_partition_preserve_global_index was introduced starting from V4.3.5 BP2. This configuration item controls whether global indexes remain valid during TRUNCATE/DROP operations on the main table partition (referred to as partition DDL operations). For upgraded tenants, the default value of this configuration item is False, while for new tenants, the default value is True. The behavior 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 a partition on concurrent DDL/DML/queries

Truncating a partition is an offline DDL operation. During the truncation, OceanBase Database adds an exclusive lock at the partition level on the target partition.

Truncate a partition

You can truncate a partition from a partitioned or subpartitioned table as needed.

Syntax

ALTER TABLE table_name TRUNCATE PARTITION partition_name_list [UPDATE GLOBAL INDEXES];

partition_name_list:
  partition_name [, partition_name ...]

Examples

• Clear the data in the M202001 and M202002 partitions of the tbl1_r partitioned table, and update the global indexes.

  obclient> CREATE TABLE tbl1_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
         PARTITION BY RANGE(log_date) 
          (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
           , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
           , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
           , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
           , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
           , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
           , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
           , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
           , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
           , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
           , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
           , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
           , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
           );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1_r TRUNCATE PARTITION M202001,M202002 UPDATE GLOBAL INDEXES;
  Query OK, 0 rows affected
  

• Truncate the p0 and p1 partitions of the RANGE-LIST-subpartitioned table t2_f_rl.

  obclient> ALTER TABLE t2_f_rl TRUNCATE PARTITION p0,p1;
  

Truncate a subpartition

You can truncate a partition from a subpartitioned table as needed.

Syntax

ALTER TABLE table_name TRUNCATE SUBPARTITION subpartition_name_list[ UPDATE GLOBAL INDEXES ];

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Example

Clear the data in the sp3 and sp4 subpartitions under the p1 partition of the t2_f_rl partitioned table, and update the global indexes.

obclient> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES('01'),
          SUBPARTITION sp1 VALUES('02')
          ),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp2 VALUES('01'),
          SUBPARTITION sp3 VALUES('02'),
          SUBPARTITION sp4 VALUES('03')
         )
        );
Query OK, 0 rows affected

obclient> ALTER TABLE t2_f_rl TRUNCATE SUBPARTITION sp3,sp4 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected