Drop partitions

You can drop partitions in a partitioned table as needed. Dropping a partition will also drop the partition's definition and data.

Support for dropping partitions in partitioned tables

In Oracle mode of OceanBase Database:

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

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

The following table describes the support for dropping partitions in partitioned tables in Oracle mode of OceanBase Database.

Considerations

When you drop 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 yet completed.

Impact of dropping a partition on global indexes

In Oracle mode, when you drop a partition of a partitioned table with global indexes, you must specify the UPDATE GLOBAL INDEXES clause in the ALTER TABLE statement to update the global indexes. If you do not specify the UPDATE GLOBAL INDEXES clause, the global indexes on the partitioned table will be unavailable after the partition is dropped.

For a table that is partitioned by RANGE, INTERVAL, or LIST (including partitioned and subpartitioned tables) and supports dropping partitions, when you specify the UPDATE GLOBAL INDEXES clause, the system does not rebuild the global indexes. Instead, it uses a lazy (delayed) maintenance strategy to keep the indexes valid by deleting data in the background. This avoids query interruptions or performance degradation caused by index invalidation. Note that this optimization applies only 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). In this case, you cannot 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

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 global indexes remain valid during TRUNCATE or DROP operations on the main table's partitions (referred to as partition DDL operations). For tenants upgraded to V4.3.5 BP2, the default value of this configuration item is False. For newly created tenants, the default value is True. The behavior of this configuration item is as follows:

Here are some examples:

• Global indexes remain valid.

  obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = True;
  

• Global indexes become invalid.

  obclient> ALTER SYSTEM SET "_ob_enable_truncate_partition_preserve_global_index" = False;
  

Impact of dropping a partition on concurrent DDL/DML/queries

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

Drop a partition in a partitioned table

You can drop a partition in a partitioned table as needed.

Syntax

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

partition_name_list:
  partition_name [, partition_name ...]

Examples

• Drop the M202005 and M202006 partitions in the tbl1_r partitioned table.

  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 DROP PARTITION M202005,M202006;
  Query OK, 0 rows affected
  

• Drop the p0 partition in the t2_f_rr partitioned table.

  obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
         PARTITION BY RANGE(col1)
         SUBPARTITION BY RANGE(col2)
          (PARTITION p0 VALUES LESS THAN(100)
            (SUBPARTITION sp0 VALUES LESS THAN(2020),
             SUBPARTITION sp1 VALUES LESS THAN(2021)
            ),
           PARTITION p1 VALUES LESS THAN(200)
            (SUBPARTITION sp2 VALUES LESS THAN(2020),
             SUBPARTITION sp3 VALUES LESS THAN(2021),
             SUBPARTITION sp4 VALUES LESS THAN(2022)
             )
           );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t2_f_rr DROP PARTITION p0;
  Query OK, 0 rows affected
  

Drop a subpartition in a subpartitioned table

You can drop a subpartition in a subpartitioned table as needed. Dropping a subpartition will also drop the subpartition's definition and data.

Syntax

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

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Examples

Drop the sp5 and sp6 subpartitions under the p1 partition in the t2_f_rr subpartitioned table and update the global indexes.

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
        (PARTITION p0 VALUES LESS THAN(100)
          (SUBPARTITION sp0 VALUES LESS THAN(2020),
           SUBPARTITION sp1 VALUES LESS THAN(2021)
          ),
         PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION sp2 VALUES LESS THAN(2020),
           SUBPARTITION sp3 VALUES LESS THAN(2021),
           SUBPARTITION sp4 VALUES LESS THAN(2022),
           SUBPARTITION sp5 VALUES LESS THAN(2023),
           SUBPARTITION sp6 VALUES LESS THAN(2024)
           )
         );
Query OK, 0 rows affected

obclient> ALTER TABLE t2_f_rr DROP SUBPARTITION sp5,sp6 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected