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 OceanBase Database in Oracle mode:

• For a partitioned table, you can truncate only RANGE or LIST partitions, but not HASH partitions.

• For a subpartitioned table, you can truncate only RANGE/LIST subpartitions.

The following table describes whether you can truncate partitions in a partitioned or subpartitioned table in OceanBase Database in Oracle mode.

Truncate partition

Before you truncate a partition, make sure that no active transaction or query is running on the partition. Otherwise, an error may occur during the execution of an SQL statement, or an unexpected situation may occur. You can query the oceanbase.GV$OB_TRANSACTION_PARTICIPANTS view in the sys tenant to obtain the status of transactions that are not completed.

Impact of truncating a partition on global indexes

In Oracle mode, if a partitioned or subpartitioned table has global indexes, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes when you truncate a partition. If you do not add this keyword, the global indexes on the partitioned or subpartitioned table will be unavailable after the partition is truncated.

In OceanBase Database V4.3.5, starting from V4.3.5 BP2, when you specify UPDATE GLOBAL INDEXES for a partitioned or subpartitioned table that uses RANGE or LIST partitioning and supports the TRUNCATE statement, the system does not rebuild the global indexes. Instead, it uses a lazy maintenance strategy to delete data in the background, keeping the indexes valid. 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 partitioned table is a HASH-partitioned table (even if the subpartitions are RANGE or LIST partitions), and you truncate a subpartition.
• The partitioning key is an expression, not a regular column.

Hidden configuration item for controlling the behavior of global indexes during partition DDL operations

In OceanBase Database V4.3.5, starting from V4.3.5 BP2, a tenant-level hidden configuration item named _ob_enable_truncate_partition_preserve_global_index is introduced. This configuration item controls whether global indexes remain valid during TRUNCATE or DROP operations on a partition (referred to as partition DDL operations). For tenants upgraded to V4.3.5, the default value of this configuration item is False. For newly created tenants, the default value is True. The following table describes the behavior of this configuration item.

Examples:

• Maintain the global indexes.

  obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = True;
  

• Inactivate the global indexes.

  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 this operation, OceanBase Database adds an exclusive lock at the partition level on the target partition.

Truncate a partition

You can truncate a partition in 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 partitioned table tbl1_r 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
  

• Clear the data in the p0 and p1 partitions of the subpartitioned table t2_f_rl that uses RANGE + LIST partitioning.

  obclient> ALTER TABLE t2_f_rl TRUNCATE PARTITION p0,p1;
  

Truncate subpartitions

You can truncate subpartitions of 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

Truncate the subpartitions sp3 and sp4 in the partition p1 of the partitioned table t2_f_lr 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