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_options:
    {PARTITION partition_name VALUES LESS THAN range_partition_expr
       [,PARTITION partition_name VALUES LESS THAN range_partition_expr]... }
   |{PARTITION partition_name VALUES IN list_partition_expr
       [,PARTITION partition_name VALUES IN list_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;
  
  partition_options:
    {PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)
       [,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)]... }
   |{PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)
       [,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)]...}
  
  subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES IN 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 partitions p3 and p4 to a template-based subpartitioned table t_m_rcrc for which the partitioning and subpartitioning strategies are both set to RANGE COLUMNS.

  obclient> ALTER TABLE t_m_rcrc ADD PARTITION
         (PARTITION p3 VALUES LESS THAN(400),
          PARTITION p4 VALUES LESS THAN(500)
         );
  Query OK, 0 rows affected
  

• Add the partition p2 to a non-template-based subpartitioned table t2_f_rclc for which the partitioning and subpartitioning strategies are both set to RANGE.

  obclient> ALTER TABLE t_f_rclc ADD PARTITION
         (PARTITION p2 VALUES LESS THAN(300)
           (SUBPARTITION sp6 VALUES IN(1,3),
            SUBPARTITION sp7 VALUES IN(4,6),
            SUBPARTITION sp8 VALUES IN(7,9))
         );
  Query OK, 0 rows affected
  

Add a subpartition

OceanBase Database in MySQL mode does not allow you to add subpartitions to a table.

Drop a partition

Syntax

ALTER TABLE table_name DROP PARTITION 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.

Examples

• Drop partitions p3 and p4 from the template-based subpartitioned table t_m_rcrc for which the partitioning and subpartitioning strategies are both set to RANGE COLUMNS.

  obclient> ALTER TABLE t_m_rcrc DROP PARTITION p3,p4;
  Query OK, 0 rows affected
  

• Drop the partition p2 from the non-template-based subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

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

Drop a subpartition

Syntax

ALTER TABLE table_name DROP SUBPARTITION 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.

• If you want to drop multiple subpartitions at a time, they must belong to the same partition.

Examples

Drop subpartitions sp6 and sp7 from the non-template-based subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

obclient> ALTER TABLE t2_f_rr DROP SUBPARTITION sp6,sp7;
Query OK, 0 rows affected

Truncate a partition

Syntax

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

Note

• 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.

• OceanBase Database allows you to remove all corresponding subpartition data from one or more partitions.

Examples

Clear the data in the p0 partition of the subpartitioned table t2_f_rclc for which the partitioning and subpartitioning strategies are set to RANGE COLUMNS and LIST COLUMNS respectively.

obclient> ALTER TABLE t2_f_rclc TRUNCATE PARTITION p0;
Query OK, 0 rows affected

Truncate a subpartition

Syntax

ALTER TABLE table_name TRUNCATE SUBPARTITION 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 remove data from one or more subpartitions.

• If you want to truncate multiple subpartitions at a time, they must belong to the same partition.

Examples

Clear the data in subpartitions sp1 and sp2 of the subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

obclient> ALTER TABLE t2_f_rr TRUNCATE SUBPARTITION sp1,sp2;
Query OK, 0 rows affected