ALTER TABLEGROUP

Purpose

You can use this statement to add multiple tables to a table group and modify the SHARDING attribute 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 a 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';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50))
      PARTITION BY LIST(col1) SUBPARTITION BY LIST (col2)
        SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES ('01'),
         SUBPARTITION mp1 VALUES ('02'),
         SUBPARTITION mp2 VALUES ('03')
         )
        (PARTITION p0 VALUES('01'),
         PARTITION p1 VALUES('02')
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
      PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
        SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES('01'),
         SUBPARTITION mp1 VALUES('02'),
         SUBPARTITION mp2 VALUES('03')
        )
        (PARTITION p0 VALUES('01'),
         PARTITION p1 VALUES('02')
        );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup1 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  The partitioning methods of tbl1 and tbl2 are the same. Therefore, the SHARDING attribute can be set to PARTITION or ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  

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

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
      PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
        SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES('01'),
         SUBPARTITION mp1 VALUES('02'),
         SUBPARTITION mp2 VALUES('03')
        )
        (PARTITION p0 VALUES('01'),
         PARTITION p1 VALUES('02')
        );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup2 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  The table group tblgroup2 contains both a non-partitioned table and a partitioned table. Therefore, the SHARDING attribute cannot be set to PARTITION or ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  OBE-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed
  
  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'ADAPTIVE';
  OBE-00600: internal error code, arguments: -4179, 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';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST (col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES ('01'),
        SUBPARTITION mp1 VALUES ('02')
        )
       (PARTITION p0 VALUES('01'),
        PARTITION p1 VALUES('02')
       );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES('01'),
        SUBPARTITION mp1 VALUES('02'),
        SUBPARTITION mp2 VALUES('03')
       )
       (PARTITION p0 VALUES('01'),
        PARTITION p1 VALUES('02')
       );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup3 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  The partitioning types of the tbl1 and tbl2 tables in the table group tblgroup3 are the same, while their subpartitioning types are different (with different numbers of subpartitions). Therefore, the SHARDING attribute can be set to PARTITION, but not ADAPTIVE.

  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
  OBE-00600: internal error code, arguments: -4179, 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 tbl1 and tbl2 tables in the table group. Then, tables in the table group are partitioned differently. The new table tbl3 cannot be added to the table group.

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 int,col2 int)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(2021),
           SUBPARTITION mp1 VALUES LESS THAN(2022),
           SUBPARTITION mp2 VALUES LESS THAN(2023)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200)
          );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2021),
          SUBPARTITION mp1 VALUES LESS THAN(2022),
          SUBPARTITION mp2 VALUES LESS THAN(2023)
         )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200)
        );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1 ADD PARTITION p3 VALUES LESS THAN(400), PARTITION p4 VALUES LESS THAN(500);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl2 DROP SUBPARTITION p0smp0,p0smp1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(col1 int,col2 int)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES LESS THAN(2021),
        SUBPARTITION mp1 VALUES LESS THAN(2022),
        SUBPARTITION mp2 VALUES LESS THAN(2023)
       )
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200)
        );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl3;
  OBE-00600: internal error code, arguments: -4179, partition num not equal, add table to tablegroup not allowed
  

References

• CREATE TABLEGROUP

• DROP TABLEGROUP

• Manage tables in a table group

• Query information about a table group