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.

• Specify the locality of the 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_tg_partition_option;
  
  alter_tg_partition_option:
    DROP PARTITION partition_name [, partition_name...]
    | ADD PARTITION opt_range_partition_list
    | modify_tg_partition_info
  

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

  ALTER TABLEGROUP tablegroup_name alter_tablegroup_action [, alter_tablegroup_action];
  
  alter_tablegroup_action:
    SET LOCALITY [=] locality_name
  

• Add a non-template-based partition.

  ALTER TABLEGROUP tablegroup_name ADD PARTITION partition_options;
  
  partition_options:
    {partition_name VALUES LESS THAN range_partition_expr [tg_subpartition_option]}
   |{partition_name VALUES list_partition_expr [tg_subpartition_option]}
  
  tg_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES list_partition_expr, ....}
   |{SUBPARTITION subpartition_name, ....}
  

  Note

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

• Add a non-template-based subpartition.

  ALTER TABLEGROUP tablegroup_name MODIFY PARTITION partition_name
    ADD SUBPARTITION tg_subpartition_option;
  
  tg_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES list_partition_expr, ...}
  

• Drop a non-template-based partition.

  ALTER TABLEGROUP tablegroup_name
  DROP PARTITION partition_name[,partition_name...] [UPDATE GLOBAL INDEXES];
  

• Drop a non-template-based subpartition.

  ALTER TABLEGROUP tablegroup_name
  DROP SUBPARTITION subpartition_name[,subpartition_name ...] [UPDATE GLOBAL INDEXES];
  

Parameters

Examples

• Create a table group named tblgroup1, change the locality of the table group to F@zone1, and create two relational tables respectively named tbl1 and tbl2.

  obclient> CREATE TABLEGROUP tblgroup1 LOCALITY='F,R{ALL_SERVER}@zone1' PARTITION BY HASH PARTITIONS 10;
  Query OK, 0 rows affected
  
  obclient> SELECT LOCALITY FROM sys.all_virtual_tablegroup_agent WHERE tablegroup_name ='TBLGROUP1';
  +------------------------------------+
  | LOCALITY                           |
  +------------------------------------+
  | FULL{1},READONLY{ALL_SERVER}@zone1 |
  +------------------------------------+
  1 row in set
  
  obclient> ALTER TABLEGROUP tblgroup1 SET LOCALITY ='F@zone1';
  Query OK, 0 rows affected
  
  obclient> SELECT LOCALITY FROM sys.all_virtual_tablegroup_agent WHERE tablegroup_name ='TBLGROUP1';
  +------------+
  | LOCALITY  |
  +------------+
  | FULL{1}@zone1 |
  +------------+
  1 row in set
  
  obclient> CREATE TABLE tbl1(col1 INT, col2 INT) TABLEGROUP = tblgroup1 LOCALITY = 'F@zone1' PARTITION BY HASH(col1) PARTITIONS 10;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT, col2 INT) TABLEGROUP = tblgroup1 LOCALITY = 'F@zone1' PARTITION BY HASH(col2) PARTITIONS 10;
  Query OK, 0 rows affected
  

• Drop the p2 partition from the tblgroup2 table group.

  obclient> ALTER TABLEGROUP tblgroup2 DROP PARTITION p2;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLEGROUP tblgroup2\G
  *************************** 1. row ***************************
         TABLEGROUP: TBLGROUP2
  CREATE TABLEGROUP: CREATE TABLEGROUP "TBLGROUP2"  BINDING = FALSE
    partition by range columns 1 subpartition by range columns 1
  (partition P0 values less than (100) (
  subpartition SP1 values less than (2019),
  subpartition SP2 values less than (2020),
  subpartition SP3 values less than (2021)),
  partition P1 values less than (200) (
  subpartition SP5 values less than (2019),
  subpartition SP6 values less than (2020),
  subpartition SP7 values less than (2021)))
  1 row in set
  

• Add the p2 partition to the tblgroup3 table group.

  obclient> ALTER TABLEGROUP tblgroup3 ADD PARTITION p2 VALUES LESS THAN (300);
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLEGROUP tblgroup3\G
  *************************** 1. row ***************************
         TABLEGROUP: TBLGROUP3
  CREATE TABLEGROUP: CREATE TABLEGROUP "TBLGROUP3"  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
  

• 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  1
      SUBPARTITION BY RANGE  1
      ( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
        ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
        )
      , PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
        ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
        )
      , PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
        ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
        )
      , PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
        ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
        )
      , PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
        ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
        , SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
        , SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
        )
      , PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
        ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
        , SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
        , SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
        )
      );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tg1 ADD PARTITION p_2007_jan              
      VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
                 ( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))
                 , SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
                 , SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))
                 ) ;
  Query OK, 0 rows affected
  

• Add non-template-based RANGE COLUMNS-RANGE COLUMNS subpartitions to a table group.

  obclient> ALTER TABLEGROUP tg1  MODIFY PARTITION p_2007_jan
      ADD SUBPARTITION p07_jan_f VALUES LESS THAN (TO_DATE('01-JUN-2007','dd-MON-yyyy')),
      SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE);
  Query OK, 0 rows affected
  

• Drop a subpartition from a table group.

  obclient> ALTER TABLEGROUP tg1 drop subpartition p07_jan_e;
  Query OK, 0 rows affected
  

• Drop a partition from a table group.

  obclient> ALTER TABLEGROUP tg1 drop partition p_2007_jan;
  Query OK, 0 rows affected
  

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