Drop a partition

You can drop partitions from a partitioned table as needed. When you drop a partition, its definition and data are also dropped.

Support for dropping partitions from partitioned tables

Take note of the following points in the Oracle mode of OceanBase Database:

• You can drop partitions only from RANGE- and LIST-partitioned tables. You cannot drop partitions from HASH partitioned tables.

• In the case of subpartitioned tables, you can drop partitions only from RANGE-LIST partitioned tables.

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

Considerations

Before you drop a partition, note that:

• Before you drop a partition, ensure that no active transactions or queries exist in this partition. Otherwise, SQL statement errors or exceptions may occur. You can query the oceanbase.GV$OB_TRANSACTION_PARTICIPANTS view for the context status of unfinished transactions in the sys tenant.

• In the Oracle mode of OceanBase Database, when you truncate a partition from a partitioned or subpartitioned table with global indexes, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this partitioned table become unavailable after the partition is dropped.

Drop a partition

You can drop a partition from a partitioned or subpartitioned 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 from 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 from the t2_f_rr subpartitioned 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

You can drop a subpartition from a subpartitioned table as needed. When you drop a subpartition, its definition and data are also dropped.

Syntax

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

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Example

Drop the sp5 and sp6 subpartitions from the p1 partition of the t2_f_rr subpartitioned table, and then 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)
           )
         );
Query OK, 0 rows affected

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