Manage a subpartitioned table group

After creating a subpartitioned table group, you can add subpartitioned tables to the group and perform partition management operations on the table group.

Support for partition management operations

Add a partition or subpartition

The following table describes the support for adding a partition to a template-based and non-template-based subpartitioned table group.

Drop a partition or subpartition

The following table describes the support for dropping a partition from a template-based subpartitioned table group.

The following table describes the support for dropping a partition from a non-template-based subpartitioned table group.

Add a partition to a table group

After adding a subpartitioned table to a table group, you can add partitions to the table group. SQL syntax:

ALTER TABLEGROUP tablegroup_name ADD PARTITION partition_option;

partition_option:
  {PARTITION partition_name VALUES LESS THAN range_partition_expr (tg_subpartition_option)
     [,PARTITION partition_name VALUES LESS THAN range_partition_expr (tg_subpartition_option)]... }
 |{PARTITION partition_name VALUES IN list_partition_expr (tg_subpartition_option)
     [,PARTITION partition_name VALUES IN list_partition_expr (tg_subpartition_option)]...}

tg_subpartition_option:
  {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION partition_name VALUES IN list_partition_expr, ....}
 |{SUBPARTITION partition_name, ....}

Examples:

• Add a partition to a RANGE-RANGE-subpartitioned table group.

  obclient> CREATE TABLEGROUP tg2_f_rr
            PARTITION BY RANGE
            SUBPARTITION BY RANGE
            (
            PARTITION p0 VALUES LESS THAN (100)
            (SUBPARTITION sp0 VALUES LESS THAN (2020)
             ,SUBPARTITION sp1 VALUES LESS THAN  (2021)
             ,SUBPARTITION sp2 VALUES LESS THAN (2022)
             ,SUBPARTITION sp3 VALUES LESS THAN  (2023)
             ),
             PARTITION p1 VALUES LESS THAN (200)
            (SUBPARTITION sp4 VALUES LESS THAN (2020)
             ,SUBPARTITION sp5 VALUES LESS THAN  (2021)
             ,SUBPARTITION sp6 VALUES LESS THAN (2022)
             ,SUBPARTITION sp7 VALUES LESS THAN  (2023))
             );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tg2_f_rr ADD PARTITION
          (PARTITION p2 VALUES LESS THAN(300)
            (SUBPARTITION sp8 VALUES LESS THAN (2020),
              SUBPARTITION sp9 VALUES LESS THAN (2021)
            )
          );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_f_rcrc ADD PARTITION
         (PARTITION p2 VALUES LESS THAN(300)
           (SUBPARTITION sp8 VALUES LESS THAN (2020),
            SUBPARTITION sp9 VALUES LESS THAN (2021)
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_rl ADD PARTITION
         (PARTITION p2 VALUES LESS THAN (300)
           (SUBPARTITION sp5 VALUES IN (('US',1),('US',2)),
            SUBPARTITION sp6 VALUES IN (('CN',1),('CN',2))
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE COLUMNS-HASH-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_rh ADD PARTITION
         (PARTITION p3 VALUES LESS THAN (2020)
           (SUBPARTITION s6,
            SUBPARTITION s7
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a RANGE COLUMNS-KEY-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_rk ADD PARTITION
         (PARTITION p3 VALUES LESS THAN(400)
           (SUBPARTITION sp9,
            SUBPARTITION sp10,
            SUBPARTITION sp11
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST COLUMNS-RANGE COLUMNS-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_lr ADD PARTITION
         (PARTITION p2 VALUES IN('E','F')
           (SUBPARTITION sp8 VALUES LESS THAN(2020),
            SUBPARTITION sp9 VALUES LESS THAN(2021),
            SUBPARTITION sp10 VALUES LESS THAN(2022),
            SUBPARTITION sp11 VALUES LESS THAN(2023)
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST COLUMNS-LIST COLUMNS-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg1_ll ADD PARTITION
         (PARTITION p2 VALUES IN('H','I')
           (SUBPARTITION sp8 VALUES IN('01','03'),
            SUBPARTITION sp9 VALUES IN('04','06'),
            SUBPARTITION sp10 VALUES IN('07','09')
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST COLUMNS-HASH-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_lh ADD PARTITION
         (PARTITION p2 VALUES IN('N','R')
           (SUBPARTITION sp4,
            SUBPARTITION sp5
           )
         );
  Query OK, 0 rows affected
  

• Add a partition to a LIST COLUMNS-KEY-subpartitioned table group.

  obclient> ALTER TABLEGROUP tg2_lk ADD PARTITION
         (PARTITION p2 VALUES IN('05','06')
           (SUBPARTITION sp6,
            SUBPARTITION sp7
           )
         );
  Query OK, 0 rows affected
  

Add a subpartition to a table group

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

Drop a partition from a table group

After adding a subpartitioned table to a table group, you can drop a partition from the table group.

SQL syntax:

ALTER TABLEGROUP tablegroup_name DROP PARTITION partition_name[,partition_name]...;

In the preceding syntax, tablegroup_name indicates the table group name, and partition_name indicates the name of the partition to be dropped.

Example:

obclient> ALTER TABLEGROUP tblgroup1 DROP PARTITION p2;
Query OK, 0 rows affected

Drop a subpartition from a table group

After adding a subpartitioned table to a table group, you can drop a subpartition from the table group. If you want to drop multiple subpartitions at a time, they must belong to the same partition.

SQL syntax:

ALTER TABLEGROUP tablegroup_name DROP SUBPARTITION subpartition_name[,partition_name]...;

In the preceding syntax, tablegroup_name indicates the table group name, and subpartition_name indicates the name of the subpartition to be dropped.

Example:

obclient> ALTER TABLEGROUP tg1 DROP SUBPARTITION sp2;