Delete a partition

You can delete a partition from a partitioned table as needed.

Support for dropping partitions in partitioned tables

In MySQL mode of OceanBase Database, the following table describes whether a primary or subpartitioned table supports dropping a partition.

Considerations

Impact of dropping partitions on global indexes

For tables that use Range/Range Columns or List/List Columns partitioning (including primary 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

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

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 Database adds an exclusive lock at the partition level on the target partition.

Delete partitions of a partitioned table

You can delete partitions of a partitioned table of the RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS type. You cannot delete partitions of a HASH or KEY partitioned table.

Syntax of the SQL statement for deleting partitions of a partitioned table:

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

Here are some examples:

• Delete the M202011 and M202012 partitions in the tbl1_r partitioned table.

  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
  

• Delete the p1 and p2 partitions in the t_m_rcrc partitioned table, which is of the RANGE COLUMNS + RANGE COLUMNS template type.

  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
  

• Delete the p1 partition in the t_f_rr partitioned table, which is of the RANGE + RANGE non-template type.

  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
  

Delete subpartitions

Syntax of the SQL statement for deleting subpartitions:

ALTER TABLE table_name DROP SUBPARTITION subpartition_name[, subpartition_name ...];

Here is an example of deleting the sp6 and sp7 subpartitions in the t_f_rr partitioned table, which is of the RANGE + RANGE non-template type:

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