ALTER TABLEGROUP

Purpose

You can use this statement to perform the following operations:

• Add multiple tables to a table group.

• Modify the partitioning rules of a table group.

• Modify the locality and primary zone of a table group.

• Add and delete partitions and subpartitions.

Support for partition management operations

Syntax

• Add multiple tables to a table group.

  ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];
  

• Modify the partitioning rules of a table group.

  ALTER TABLEGROUP tablegroup_name alter_tablegroup_partition_option;
  
  alter_tablegroup_partition_option:
      DROP PARTITION '(' name_list ')'
    | ADD PARTITION opt_range_partition_list
  

• Modify the locality and primary zone of a table group.

  ALTER TABLEGROUP tablegroup_name alter_tablegroup_actions [, alter_tablegroup_action ...];
  
  alter_tablegroup_action:
      SET LOCALITY [=] locality
  

• Add a non-template-based partition.

  ALTER TABLEGROUP tablegroup_name ADD PARTITION partition_options;
  
  partition_options:
    {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 subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES IN list_partition_expr, ....}
   |{SUBPARTITION subpartition_name, ....}
  

  Note

  When you add a partition to a subpartition table group, you must add subpartitions in the partition.

• Drop a non-template-based partition.

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

• Drop a non-template-based subpartition.

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

Parameters

Examples

• Create a table group named tgh and change its locality to F@zone1.

  obclient> CREATE TABLEGROUP tgh LOCALITY='F,R{ALL_SERVER}@zone1' PARTITION BY HASH PARTITIONS 10;
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tgh SET LOCALITY ='F@zone1';
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-RANGE COLUMNS subpartition without using a template, and add a partition to a table group.

  obclient> CREATE TABLEGROUP tg1
        PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY RANGE COLUMNS 1
       (
       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 tg1 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
  

• Drop a subpartition from a table group.

  obclient> ALTER TABLEGROUP tg1 DROP SUBPARTITION sp2;
  Query OK, 0 rows affected
  

• Drop a partition from a table group.

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

For more information about how to create a partition, see Manage partitions in OceanBase Database Administrator Guide.