You can truncate partitions in a partitioned table to clear the data in the partitions while retaining the partition structure.
Support for truncating partitions
The following table describes the support for truncating partitions in partitioned and subpartitioned tables.
Partitioned table |
Partitioning type |
Truncate primary partition |
Truncate subpartition |
|---|---|---|---|
| Primary partitioned table |
|
Supported | - |
| Primary partitioned table |
|
Not supported | - |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Supported | Supported |
| Subpartitioned table |
|
Supported | Not supported |
| Subpartitioned table |
|
Not supported | Supported |
| Subpartitioned table |
|
Not supported | Not supported |
| Subpartitioned table |
|
Not supported | Supported |
| Subpartitioned table |
|
Not supported | Not supported |
Considerations
Impact of truncating partitions on global indexes
For tables that use RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partitioning (including partitions and subpartitions) 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 subpartition is a RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partition), and the
TRUNCATEoperation is performed on the subpartition. - The partitioning key is an expression, not a regular column.
Hidden configuration item for controlling global index behavior during partition DDL
The tenant-level hidden configuration item _ob_enable_truncate_partition_preserve_global_index controls whether global indexes remain valid during TRUNCATE/DROP operations on table partitions (referred to as partition DDL). The behavior based on the configuration item value is as follows:
Configuration item value |
Behavior during partition DDL execution |
Result after partition DDL execution |
|---|---|---|
| True | Only the primary partitions on which the TRUNCATE/DROP operation is performed are not allowed to perform DML operations, while other partitions of the primary table can perform DML operations normally. |
Global indexes remain valid: DML operations can be performed on the primary table and all index tables, and global indexes related to the primary table remain valid. |
| False | Only the primary partitions on which the TRUNCATE/DROP operation is performed are not allowed to perform DML operations, while other partitions of the primary table can perform DML operations normally. |
Global indexes become invalid: DML operations can be performed on the primary table and all index tables, but global indexes related to the primary table become invalid or need to be rebuilt. |
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 AI Database adds an exclusive lock at the partition level on the target partition.
Operation type |
Impact during the execution of truncating partitions |
|---|---|
| Concurrent DDL | Prohibited or blocked. No other DDL operations on the same table can be executed concurrently. |
| Concurrent DML |
|
Concurrent queries (SELECT) |
Not affected, can be executed normally. |
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 ...];
Note
When you truncate a partition, you can clear the data in one or more partitions.
Before you truncate a partition, make sure that no active transaction or query is running on the partition. Otherwise, an error may occur when you execute the SQL statement, or an unexpected situation may occur. In the
systenant, you can query the current context status of uncompleted transactions from theoceanbase.GV$OB_TRANSACTION_PARTICIPANTSview.When you truncate a partition of a subpartitioned table, the data in the partition and its corresponding subpartitions are cleared.
Examples:
Truncate the data in the
M202001andM202002partitions of the partitioned tabletbl1_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 affectedTruncate the data in the
p0partition of the RANGE COLUMNS + LIST COLUMNS partitioned tablet_f_rclc.
Truncate a subpartition
The SQL syntax for truncating a subpartition is as follows:
ALTER TABLE table_name TRUNCATE SUBPARTITION subpartition_name[, subpartition_name ...];
Note
Before you truncate a subpartition, make sure that no active transaction or query is running on the subpartition. Otherwise, an error may occur when you execute the SQL statement, or an unexpected situation may occur. In the
systenant, you can query the current context status of uncompleted transactions from theoceanbase.GV$OB_TRANSACTION_PARTICIPANTSview.When you truncate multiple subpartitions, the subpartitions must belong to the same partition.
The following example shows how to truncate the data in the sp1 and sp2 subpartitions of the RANGE + RANGE subpartitioned 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