Manage a subpartitioned table

After you create a subpartitioned table, you can add, drop, or truncate partitions and subpartitions in the subpartitioned table.

Support for partition management operations

Add a partition or subpartition

Drop a partition or subpartition

Truncate a partition or subpartition

Add a partition

Syntax

You can add a partition to a template-based or non-template-based subpartitioned table.

• Template-based subpartitioned table

  ALTER TABLE table_name ADD PARTITION partition_options;
  
  partition_option:
    range_partition_option | list_partition_option
  
  range_partition_option:
    partition_name VALUES LESS THAN partition_expr
  
  list_partition_option:
    partition_name VALUES partition_expr
  

  Note

  For a template-based subpartitioned table, you only need to specify the partition definition when you add a partition. The subpartition definition is automatically filled based on the template.

• Non-template-based subpartitioned table

  ALTER TABLE table_name ADD PARTITION partition_options(subpartition_option);
  
  partition_option:
    range_partition_option | list_partition_option
  
  range_partition_option:
    partition_name VALUES LESS THAN partition_expr
  
  list_partition_option:
    partition_name VALUES partition_expr
  
  tg_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES list_partition_expr, ....}
   |{SUBPARTITION subpartition_name, ....}
  

  Note

  For a non-template-based subpartitioned table, you must specify both partition and subpartition definitions when you add a partition.

Examples

• Add the partition p2 to the template-based RANGE-RANGE-subpartitioned table t2_m_rr.

  obclient> ALTER TABLE t2_m_rr ADD PARTITION p2 VALUES LESS THAN(300);
  Query OK, 0 rows affected
  

• Add the partition p2 to the non-template-based RANGE-RANGE-subpartitioned table t2_f_rr.

  obclient> ALTER TABLE t2_f_rr ADD PARTITION p2 VALUES LESS THAN(300)
         (SUBPARTITION sp5 VALUES LESS THAN(2020),
          SUBPARTITION sp6 VALUES LESS THAN(2021),
          SUBPARTITION sp7 VALUES LESS THAN(2022)
          );
  Query OK, 0 rows affected
  

Add a subpartition

Syntax

ALTER TABLE table_name MODIFY PARTITION partition_name
  ADD SUBPARTITION subpartition_option;

subpartition_option:
  {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

Note

OceanBase Database allows you to add subpartitions only to a non-template-based subpartitioned table in Oracle mode.

Parameters

Examples

Add the subpartitions sp5 and sp6 to the partition p1 of the non-template-based RANGE-RANGE-subpartitioned table t2_f_rr.

obclient> ALTER TABLE t2_f_rr MODIFY PARTITION p1 ADD
       SUBPARTITION sp5 VALUES LESS THAN(2023),
       SUBPARTITION sp6 VALUES LESS THAN(2024);
Query OK, 0 rows affected

Drop a partition

Syntax

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

partition_name_list:
  partition_name[, partition_name ...]

Note

• Before you drop a partition from a subpartitioned table, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur. In the sys tenant, you can query the context status of unfinished transactions in the __all_virtual_trans_stat table.

• If you drop a partition, its definition, data, and subpartitions are also dropped.

• When you drop a partition from a subpartitioned table with global indexes in Oracle mode, you need to 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 subpartitioned table become unavailable after the partition is dropped.

Examples

Drop the p0 partition from the t2_f_rr subpartitioned table.

obclient> ALTER TABLE t2_f_rr DROP PARTITION p0;
Query OK, 0 rows affected

Drop a subpartition

Syntax

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

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Note

• Before you drop a subpartition from a subpartitioned table, ensure that no active transaction or query exists in this subpartition. Otherwise, SQL statement errors or exceptions may occur. In the sys tenant, you can query the context status of unfinished transactions in the __all_virtual_trans_stat table.

• If you drop a subpartition, its definition and data are also dropped.

• When you drop a subpartition from a subpartitioned table with global indexes in Oracle mode, you need to 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 subpartitioned table become unavailable after the subpartition is dropped.

Examples

Drop the sp5 and sp6 subpartitions from the p1 partition of the t2_f_rr subpartitioned table, and then update the global indexes.

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

Truncate a partition

Syntax

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

partition_name_list:
  partition_name[, partition_name ...]

Note

• OceanBase Database allows you to truncate one or more partitions in a subpartitioned table of the RANGE or LIST partitioning method to clear all data in the subpartitions corresponding to one or more partitions. Supported subpartitioning methods of RANGE or LIST are RANGE-RANGE, RANGE-LIST, LIST-RANGE, and LIST-LIST.

• Before you truncate a partition from a subpartitioned table, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur. In the sys tenant, you can query the context status of unfinished transactions in the __all_virtual_trans_stat table.

• When you truncate a partition from a subpartitioned table with global indexes in Oracle mode, you need to 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 subpartitioned table become unavailable after the partition is truncated.

Examples

Truncate the partitions p0 and p1 of the RANGE-LIST-subpartitioned table t2_f_rl.

obclient> ALTER TABLE t2_f_rl TRUNCATE PARTITION p0,p1;

Truncate a subpartition

Syntax

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

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Note

• Before you truncate a subpartition from a subpartitioned table, ensure that no active transaction or query exists in this subpartition. Otherwise, SQL statement errors or exceptions may occur. In the sys tenant, you can query the context status of unfinished transactions in the __all_virtual_trans_stat table.

• OceanBase Database allows you to truncate one or more subpartitions in a subpartitioned table of the RANGE or LIST partitioning method to clear all data in one or more subpartitions. Supported subpartitioning methods of RANGE or LIST are RANGE-RANGE, RANGE-LIST, LIST-RANGE, and LIST-LIST.

• When you truncate a subpartition from a subpartitioned table with global indexes in Oracle mode, you need to 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 subpartitioned table become unavailable after the subpartition is truncated.

Examples

Truncate the sp3 and sp4 subpartitions from the p1 partition of the t2_f_lr subpartitioned table, and then update the global indexes.

obclient> ALTER TABLE t2_f_lr TRUNCATE SUBPARTITION sp3,sp4 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected