Manage a partitioned table group

After creating a partitioned table group, you can add a partitioned table to the table group, and manage the partitions by using the table group in a unified manner.

Support for partition management operations

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

Add a partition to a table group

You can add a partition to a RANGE- or LIST-partitioned table group. Ensure that you have the ALTER 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 partition_expr
    [, PARTITION partition_name VALUES partition_expr]...
   )

Syntax description:

• tablegroup_name: the table group name.

• partition_option: the partition to be added.

• range_partition_option: the RANGE partition option.

• list_partition_option: the LIST partition option.

• partition_name: the partition name.

• partition_expr: the partitioning expression.

Example:

1. Create a RANGE-partitioned table group tg1_r.

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

2. Add the partition p2 to the table group tg1_r.

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

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

   obclient> SHOW CREATE TABLEGROUP tg1_r\G
   *************************** 1. row ***************************
          TABLEGROUP: TG1_R
   CREATE TABLEGROUP: CREATE TABLEGROUP "TG1_R"  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))
   1 row in set
   

Drop a partition from a table group

You can drop a partition from a RANGE- or LIST-partitioned table group. Ensure that you have the ALTER TABLE privilege on all tables in this table group.

SQL syntax:

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

Example:

1. Create a LIST-partitioned table group tg1_l.

   obclient> CREATE TABLEGROUP tg1_l
          PARTITION BY LIST COLUMNS 1
           (PARTITION p0 VALUES('A'),
            PARTITION p1 VALUES('B'),
            PARTITION p2 VALUES('C'),
            PARTITION p3 VALUES('D'),
            PARTITION p4 VALUES('E'),
            PARTITION p5 VALUES('F')
           );
   Query OK, 0 rows affected
   

2. Drop partitions p4 and p5 from the table group tg1_l.

   obclient> ALTER TABLEGROUP tg1_l DROP PARTITION p4,p5;
   Query OK, 0 rows affected