You can drop partitions from a partitioned table based on your business requirements.
Support for dropping partitions in partitioned tables
The following table describes whether a partitioned or subpartitioned table supports dropping a partition.
Partitioned table |
Partitioning type |
Drop primary partition |
Drop 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 dropping partitions on global indexes
For tables that use RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partitioning (including partitions and subpartitions) and support dropping partitions, the system does not rebuild global indexes. Instead, it uses background compactions to lazily maintain the indexes, ensuring their validity without causing query interruptions or performance degradation due to index invalidation. Note that this optimization applies only to regular global indexes. The following scenarios do not trigger this optimization:
- The primary partition is a HASH partition (even if the subpartition is a RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partition), in which case dropping a subpartition is performed.
- The partitioning key is an expression, not a regular column.
Hidden configuration item for controlling global index behavior during partition DDL operations
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 operations). The behavior of this configuration item is as follows:
Configuration value |
Behavior during partition DDL operations |
Result after partition DDL operations complete |
|---|---|---|
| True | Only the primary partitions on which the TRUNCATE/DROP operation is performed are not allowed to perform DML operations. 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. Other partitions of the primary table can perform DML operations normally. |
Global indexes become invalid or are rebuilt: DML operations can be performed on the primary table and all index tables, but global indexes related to the primary table become invalid or are rebuilt. |
Examples:
Global index remains valid.
obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = True;Global index becomes invalid.
obclient> ALTER SYSTEM SET _ob_enable_truncate_partition_preserve_global_index = False;
Impact of dropping partitions on concurrent DDL/DML/queries
Dropping a partition 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 partition drop |
|---|---|
| Concurrent DDL | Prohibited or blocked. No other DDL operations on the same table can be performed concurrently. |
| Concurrent DML |
|
Concurrent queries (SELECT) |
Unaffected and can be executed normally. |
Drop partitions of a partitioned table
You can drop partitions of a partitioned table of the RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS type. You cannot drop partitions of a HASH or KEY partitioned table.
The SQL syntax for dropping partitions of a partitioned table is as follows:
ALTER TABLE table_name DROP PARTITION partition_name[, partition_name ...];
Note
You can drop one or more partitions of a partitioned table, but you cannot drop all partitions of the table.
Before you drop a partition, make sure that no active transaction or query is running on the partition. Otherwise, an error may be returned when you execute the SQL statement, or an unexpected situation may occur. In the
systenant, you can query the current status of ongoing transactions from theoceanbase.GV$OB_TRANSACTION_PARTICIPANTSview.When you drop a partition, the data in the partition is also dropped. If you want to drop only the data in a partition, you can execute the
TRUNCATEstatement.When you drop a partition of a subpartitioned table, the definition of the partition, its subpartitions, and the data in the subpartitions are also dropped.
Examples:
Drop the
M202011andM202012partitions from 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 DROP PARTITION M202011,M202012; Query OK, 0 rows affectedDrop the partitions
p1andp2from the template-based RANGE COLUMNS + RANGE COLUMNS subpartitioned tablet_m_rcrc.obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE (SUBPARTITION mp0 VALUES LESS THAN(1000), SUBPARTITION mp1 VALUES LESS THAN(2000), SUBPARTITION mp2 VALUES LESS THAN(3000) ) (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p2 VALUES LESS THAN(300) ); Query OK, 0 rows affected obclient> ALTER TABLE t_m_rcrc DROP PARTITION p1,p2; Query OK, 0 rows affectedDrop the partition
p1from the non-template-based RANGE + RANGE subpartitioned tablet_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 DROP PARTITION p1; Query OK, 0 rows affected
Drop a subpartition
The SQL syntax for dropping a subpartition is as follows:
ALTER TABLE table_name DROP SUBPARTITION subpartition_name[, subpartition_name ...];
Note
When dropping a subpartition from a partitioned table, ensure no active transactions or queries exist on that subpartition. Otherwise, SQL statements may report errors or unexpected behavior may occur. In the
systenant, query the status of currently unended transaction contexts using the viewoceanbase.GV$OB_TRANSACTION_PARTICIPANTS.Dropping a subpartition also drops its definition and all data within it.
When dropping multiple subpartitions, they must all belong to the same partition.
The following example demonstrates dropping the subpartitions sp6 and sp7 from the non-template-based 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 DROP SUBPARTITION sp6,sp7;
Query OK, 0 rows affected