Subpartitioned tables

After you create a subpartitioned table, you can add, delete, or truncate the subpartitioned table.

Add a partition

Two scenarios exist:

• Add a partition and a subpartition by using a template

  When you use a template, you only need to specify the partitioning method of the primary table. The system generates the definition of the subpartitioning method based on the template.

  Syntax:

  ALTER TABLE table_name add partition part_name partition_define
  

  Example:

  obclient> ALTER TABLE t_range_range add partition p3 VALUES less than (400);
  

• Add a partition and a subpartition without using a template

  To add a partition and a subpartition without using a template, you must specify the partitioning and subpartitioning methods.

  Syntax:

  ALTER TABLE table_name add partition part_name partition_define
  (
   subpartition subpart_name subpartition_define
   , ...
   )
  

  Example:

  obclient> ALTER TABLE t_range_range1 add partition p4 VALUES less than (500) 
  (
   subpartition p4_r1 VALUES less than (100),
   subpartition p4_r2 VALUES less than (200),
   subpartition p5_r3 VALUES less than (300)
   );
  

Add a subpartition

In the current OceanBase Database version, you can add subpartitions only for a non-template-based subpartitioned table in Oracle mode.

Syntax:

ALTER TABLE table_name modify partition part_name add subpartition subpart_name subpartition_define

Example:

obclient> ALTER TABLE t_range_range1 modify partition p1 add subpartition p1_r4 VALUES less than (400);

Drop a partition

OceanBase Database allows you to drop one or more partitions from a rang- or list-subpartitioned table. If you drop a partition, the partitioning method of the partition and the data in the subpartitions of this partition will also be dropped.

Run the following statements to drop partitions in MySQL or Oracle mode:

obclient> ALTER TABLE table_name DROP PARTITION p1;

obclient> ALTER TABLE table_name DROP PARTITION p1,p2;

In addition, in Oracle mode, for a subpartitioned table that contains global indexes, when you drop a partition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

obclient> ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES;

obclient> ALTER TABLE table_name DROP PARTITION p1,p2 UPDATE GLOBAL INDEXES;

Notice

If you do not add the UPDATE GLOBAL INDEXES keyword, the global indexes in the subpartitioned table will become unavailable after the partition is dropped.

Drop a subpartition

OceanBase Database allows you to drop one or more subpartitions from a non-template-based subpartitioned table and a range- or list-subpartitioned table. If you drop a subpartition, the subpartitioning method of the subpartition and the data in the subpartition will also be dropped.

Run the following statements to truncate subpartitions in MySQL or Oracle mode:

obclient> ALTER TABLE table_name DROP SUBPARTITION p1;

obclient> ALTER TABLE table_name DROP SUBPARTITION p1,p2;

In Oracle mode, for a subpartitioned table that contains global indexes, when you drop a subpartition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

obclient> ALTER TABLE table_name DROP SUBPARTITION p1 UPDATE GLOBAL INDEXES;

obclient> ALTER TABLE table_name DROP SUBPARTITION p1,p2 UPDATE GLOBAL INDEXES;

Notice

If you do not add the UPDATE GLOBAL INDEXES keyword, the global index in the subpartitioned table will become unavailable after the subpartition is dropped.

Truncate a partition

OceanBase Database allows you to truncate one or more partitions of a Range- or List-subpartitioned table to clear all data in the subpartitions of the partitions.

Run the following statements to truncate partitions in MySQL or Oracle mode:

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1;

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1,p2;

In addition, in Oracle mode, for a subpartitioned table that contains global indexes, when you truncate a partition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1,p2 UPDATE GLOBAL INDEXES;

Notice

If you do not add the UPDATE GLOBAL INDEXES keyword, the global indexes in the subpartitioned table will become unavailable after the partition is truncated.

Truncate a subpartition

OceanBase Database allows you to truncate one or more subpartitions of a RANGE- or LIST-subpartitioned table to clear all data in the subpartitions.

Run the following statements to truncate subpartitions in MySQL or Oracle mode:

obclient> ALTER TABLE table_name TRUNCATE SUBPARTITION p1;

obclient> ALTER TABLE table_name TRUNCATE SUBPARTITION p1,p2;

In Oracle mode, for a subpartitioned table that contains global indexes, when you truncate a subpartition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

obclient> ALTER TABLE table_name TRUNCATE SUBPARTITION p1 UPDATE GLOBAL INDEXES;

obclient> ALTER TABLE table_name TRUNCATE SUBPARTITION p1,p2 UPDATE GLOBAL INDEXES;

Notice

If you do not add the UPDATE GLOBAL INDEXES keyword, the global indexes in the subpartitioned table will become unavailable after the subpartition is truncated.