Delete partitions

You can delete partitions from a partitioned table as needed.

Support for dropping partitions in partitioned tables

In MySQL mode of OceanBase Database, the support for dropping partitions in partitioned tables is as follows:

Considerations

Impact on global indexes

For V4.3.5, starting from V4.3.5 BP2, when you drop a partition of a table that is partitioned by RANGE/RANGE COLUMNS, LIST/LIST COLUMNS (including partitioned tables and subpartitioned tables), the system does not rebuild the global indexes. Instead, it uses a lazy maintenance strategy to keep the indexes valid by deleting data in the background. This avoids 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 RANGE/RANGE COLUMNS or LIST/LIST COLUMNS). In this case, dropping a subpartition is not allowed.
• The partitioning key is an expression, not a regular column.
• For full-text indexes or non-structured data types, the global indexes are unavailable.

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

For V4.3.5, OceanBase Database introduces the tenant-level hidden configuration item _ob_enable_truncate_partition_preserve_global_index starting from V4.3.5 BP2. This configuration item controls whether global indexes remain valid during TRUNCATE or DROP operations on a partition (referred to as partition DDL). For upgraded tenants, the default value is False, and for newly created tenants, the default value is True. The behavior 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 on concurrent DDL/DML/queries

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

Drop a partition

You can drop a partition from a partitioned table of the RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS type. However, you cannot drop a partition from a partitioned table of the HASH or KEY type.

The SQL syntax for dropping a partition is as follows:

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

Here are some examples:

• Drop the M202011 and M202012 partitions from 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
  

• Drop the p1 and p2 partitions from the t_m_rcrc partitioned table, which is a RANGE COLUMNS + RANGE COLUMNS partitioned table with a partitioning template.

  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
  

• Drop the p1 partition from the t_f_rr partitioned table, which is a RANGE + RANGE partitioned table without a partitioning template.

  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 dropping the sp6 and sp7 subpartitions from the t_f_rr partitioned table, which is a RANGE + RANGE partitioned table without a partitioning template:

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