Manage a partitioned table group

After adding a partitioned table to a table group, you can manage the partitions by using the table group.

Support for partition management operations

You cannot separately manage partitions of tables in a table group. Instead, you must manage the partitions by using the table group in a unified manner.

The following table describes the support for partition management operations on a partitioned table group.

Add a partition to a table group

You can add a partition to a partitioned table group. Ensure that you have the CREATE TABLE privilege on all tables in this table group.

SQL syntax:

ALTER TABLEGROUP tablegroup_name ADD PARTITION partition_option;

partition_option:
  range_partition_option | list_partition_option

range_partition_option:
  ( PARTITION partition_name VALUES LESS THAN partition_expr
   [, PARTITION partition_name VALUES LESS THAN partition_expr]...
  )

list_partition_option:
  (PARTITION partition_name VALUES IN partition_expr
    [, PARTITION partition_name VALUES IN partition_expr]...
   )

Add a partition to a RANGE COLUMNS-partitioned table group

1. Create a RANGE COLUMNS-partitioned table group.

   obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY RANGE COLUMNS 1
               (
                PARTITION p0 VALUES LESS THAN (100),
                PARTITION p1 VALUES LESS THAN (200)
               );
   Query OK, 0 rows affected
   

2. Add a partition to the table group.

   obclient> ALTER TABLEGROUP tblgroup1 ADD PARTITION
               ( PARTITION p2 VALUES LESS THAN(300),
                 PARTITION p3 VALUES LESS THAN(400),
                 PARTITION p4 VALUES LESS THAN(500)
               );
   Query OK, 0 rows affected
   

3. Execute the following statement to view information about the table group:

   obclient> SHOW CREATE TABLEGROUP tblgroup1\G
   *************************** 1. row ***************************
          Tablegroup: tblgroup1
   Create Tablegroup: CREATE TABLEGROUP IF NOT EXISTS `tblgroup1`  BINDING = FALSE
     partition by range columns 1
   (partition p0 values less than (100),
   partition p1 values less than (200),
   partition p2 values less than (300),
   partition p3 values less than (400),
   partition p4 values less than (500))
   1 row in set
   

Add a partition to a LIST COLUMNS-partitioned table group

1. Create a LIST COLUMNS-partitioned table group.

   obclient> CREATE TABLEGROUP tblgroup2 PARTITION BY LIST COLUMNS 1
               ( PARTITION p0 VALUES IN('A','B'),
                 PARTITION p1 VALUES IN('C','D')
               );
   Query OK, 0 rows affected
   

2. Add a partition to the table group.

   obclient> ALTER TABLEGROUP tblgroup2 ADD PARTITION
               ( PARTITION p2 VALUES IN('E','F'),
                 PARTITION p3 VALUES IN('H','I'),
                 PARTITION p4 VALUES IN('J','K')
                );
   Query OK, 0 rows affected
   

3. Execute the following statement to view information about the table group:

   obclient> SHOW CREATE TABLEGROUP tblgroup2\G
   *************************** 1. row ***************************
          Tablegroup: tblgroup2
   Create Tablegroup: CREATE TABLEGROUP IF NOT EXISTS `tblgroup2`  BINDING = FALSE
     partition by list columns 1
   (partition p0 values in ('A','B'),
   partition p1 values in ('C','D'),
   partition p2 values in ('E','F'),
   partition p3 values in ('H','I'),
   partition p4 values in ('J','K'))
   1 row in set
   

Drop a partition from a table group

You can drop a partition from a table group. Ensure that you have the CREATE TABLE privilege on all tables in this table group.

SQL syntax:

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

Example:

1. Create a RANGE COLUMNS-partitioned table group.

   obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY RANGE COLUMNS 1
               ( PARTITION p0 VALUES LESS THAN (100),
                 PARTITION p1 VALUES LESS THAN (200),
                 PARTITION p2 VALUES LESS THAN(300),
                 PARTITION p3 VALUES LESS THAN(400),
                 PARTITION p4 VALUES LESS THAN(500)
                );
   

2. Drop a partition from a table group.

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