Truncate a partition

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

Supported operations for truncating a partition

In OceanBase Database in Oracle mode:

• For a partitioned table, you can truncate a RANGE partition or a LIST partition, but you cannot truncate a HASH partition.

• For a subpartitioned table, you can truncate a RANGE or LIST subpartition.

The following table describes the support for truncating a partition in a partitioned table or subpartitioned table in OceanBase Database in Oracle mode.

Considerations

When you truncate a partition, make sure that there are no active transactions or queries on the partition. Otherwise, an error may occur or unexpected behavior may be observed. You can query the oceanbase.GV$OB_TRANSACTION_PARTICIPANTS view in the sys tenant to obtain the context of transactions that have not been completed.

Impact of truncate partition on global indexes

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

For a table that is partitioned by RANGE, INTERVAL, or LIST (including partitioned and subpartitioned tables) and supports the TRUNCATE operation, when you specify UPDATE GLOBAL INDEXES, the system does not rebuild the global index. Instead, it uses a lazy maintenance strategy to delete data in the background and keep the index valid. This prevents query interruptions or performance degradation caused by an invalid index. Note that this optimization is only applicable to regular global indexes. The following scenarios cannot trigger this optimization:

• The partitioned table is partitioned by HASH (even if the subpartitions are RANGE or LIST), and you truncate a subpartition.
• The partitioning key is an expression, not a regular column.

Hidden configuration items for controlling global index behavior during partition DDL operations

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

Here are some examples:

• Index remains valid.

  obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = True;
  

• Index becomes invalid.

  obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = False;
  

Impact of truncate partition on concurrent DDL/DML/Queries

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

Truncate a partition in a partitioned table

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

• Truncate the M202001 and M202002 partitions in the tbl1_r partitioned table and update the global index information.

  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 in the t2_f_rl subpartitioned table, which is partitioned by RANGE and subpartitioned by LIST.

  obclient> ALTER TABLE t2_f_rl TRUNCATE PARTITION p0,p1;
  

Truncate a subpartition

You can truncate a subpartition in 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 ...]

Examples

Truncate the sp3 and sp4 subpartitions in the p1 partition of the t2_f_lr subpartitioned table and update the global index information.

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