Drop a partition

You can drop partitions from a partitioned table as needed.

Support for dropping partitions from partitioned tables

The following table describes the support for dropping partitions from partitioned and subpartitioned tables in MySQL-compatible mode of OceanBase Database.

Instructions for dropping a partition

Impact of dropping a partition on global indexes

For tables using RANGE/RANGE COLUMNS or LIST/LIST COLUMNS partitions (including both partitions and subpartitions) that support partition deletion, the system does not rebuild global indexes. Instead, it maintains index validity via a lazy (deferred) maintenance strategy, merging and deleting data in the background. This approach prevents query disruptions or performance degradation caused by index invalidation. Note that this optimization only applies to regular global indexes. The following scenarios do not trigger this optimization:

• The partition is a HASH partition (even if the subpartition is RANGE/RANGE COLUMNS or LIST/LIST COLUMNS), and a drop partition operation is performed on the subpartition.
• The partitioning key is an expression, not a regular column.

Hidden tenant-level configuration item to control global index behavior during partition DDL

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

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 dropping a partition on concurrent DDL/DML/queries

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

Drop a partition

You can drop a partition from a RANGE-, RANGE COLUMNS-, LIST, or LIST COLUMNS-partitioned table. You cannot drop partitions from HASH- or KEY-partitioned tables.

The SQL syntax for dropping a partition is as follows:

ALTER TABLE table_name DROP PARTITION partition_name[, partition_name ...];

Here are some examples:

• Create a partitioned table named tbl1_r and drop the M202011 and M202012 partitions from it.

  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 affected
  

• Create a template-based RANGE COLUMNS-RANGE COLUMNS-subpartitioned table named t_m_rcrc and drop the p1 and p2 partitions from it.

  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 affected
  

• Create a RANGE-RANGE-subpartitioned table named t_f_rr and drop the p1 partition from it.

  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 ...];

Here is an example of creating a non-template-based RANGE-RANGE-partitioned table named t_f_rr and dropping the sp6 and sp7 subpartitions from it:

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