ALTER TABLEGROUP

Purpose

This statement is used to add multiple tables to a table group and modify the attributes of the table group.

Syntax

• Add multiple tables to a table group.

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

• Modify the partitioning rules of the table group.

  ALTER TABLEGROUP tablegroup_name tablegroup_option
  
  tablegroup_option:
      SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE'
  

Parameters

Examples

• Create a table group named tblgroup1 with the SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to the table group.

  obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02')),
         PARTITION p1 VALUES in (02)
            (SUBPARTITION mp3 VALUES in ('01'),
             SUBPARTITION mp4 VALUES in ('02'))
         );
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
          (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02')),
         PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'))
         );
  obclient> ALTER TABLEGROUP tblgroup1 ADD tbl1,tbl2;
  
  

  Since the partitioning methods of tbl1 and tbl2 are consistent, the SHARDING attribute can be changed to PARTITION or ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';
  

• Create a table group named tblgroup2 with the SHARDING attribute set to NONE, and add the non-partitioned table tbl1 and the partitioned table tbl2 to the table group.

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'NONE';
  obclient> CREATE TABLE tbl1 (col INT);
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
  obclient> ALTER TABLEGROUP tblgroup2 ADD tbl1,tbl2;
  
  

  Since the table group tblgroup2 contains both non-partitioned and partitioned tables, the SHARDING attribute cannot be changed to PARTITION or ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed
  
  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'ADAPTIVE';
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed
  

• Create a table group named tblgroup3 with the SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to the table group.

  obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'NONE';
  
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
         SUBPARTITION mp1 VALUES in ('02'),
         SUBPARTITION mp2 VALUES in ('03')
         ),
       PARTITION p1 VALUES in (02)
         (SUBPARTITION mp3 VALUES in ('01'),
          SUBPARTITION mp4 VALUES in ('02'),
          SUBPARTITION mp5 VALUES in ('03'))
       );
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
         SUBPARTITION mp1 VALUES in ('02')),
       PARTITION p1 VALUES in (02)
        (SUBPARTITION mp3 VALUES in ('01'),
         SUBPARTITION mp4 VALUES in ('02'))
       );
  obclient> ALTER TABLEGROUP tblgroup3 ADD tbl1,tbl2;
  
  

  Since the primary partitions of tbl1 and tbl2 in the table group tblgroup3 have the same partitioning type but different subpartitioning types (with different numbers of subpartitions), the SHARDING attribute can be changed to PARTITION but not to ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  
  
  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
  ERROR 4179 (HY000): subpartition num not matched, modify tablegroup sharding attribute not allowed
  

• Create a table group named tblgroup4 with the SHARDING attribute set to ADAPTIVE, and modify the number of partitions for the tables tbl1 and tbl2 in the table group. After this modification, the partitioning methods of all tables in the table group are inconsistent, and no new table tbl3 can be added to the table group.

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'ADAPTIVE';
  
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200));
  obclient> CREATE TABLE tbl2(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200));
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl1,tbl2;
  obclient> ALTER TABLE tbl1
      ADD PARTITION (PARTITION p3 VALUES LESS THAN(400),
                     PARTITION p4 VALUES LESS THAN(500));
  obclient> ALTER TABLE tbl2 DROP SUBPARTITION p0smp0,p0smp1;
  obclient> CREATE TABLE tbl3(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200));
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl3;
  ERROR 4179 (HY000): partition num not equal, add table to tablegroup not allowed
  

References

• CREATE TABLEGROUP

• DROP TABLEGROUP

• Manage tables in a table group

• View the information of a table group