CREATE TABLEGROUP

Purpose

You can use this statement to create a table group.

A table group is used to create a group of tables with the same distribution characteristics. These tables can be joined locally to avoid cross-node data requests.

Syntax

CREATE TABLEGROUP tablegroup_name [tablegroup_option];

tablegroup_option:
   SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE'

Parameters

Examples

• Create a table group named myTableGroup1 and add non-partitioned tables myt1 and myt2 to the table group.

  obclient> CREATE TABLEGROUP myTableGroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE myt1 (c1 NUMBER, c2 NUMBER) TABLEGROUP = myTableGroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE myt2 (c1 NUMBER, c2 NUMBER) TABLEGROUP = myTableGroup1;
  Query OK, 0 rows affected
  

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

  obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col NUMBER) TABLEGROUP = tblgroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
         );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(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
  

• Create a table group named tblgroup2 with the SHARDING attribute PARTITION, and add partitioned tables tbl1 and tbl2 to the table group. The two tables share the same partitioning method.

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES ('01'),
       PARTITION p1 VALUES ('02')
       );
  Query OK, 0 rows affected
  

• Create a table group named tblgroup3 with the SHARDING attribute PARTITION. The non-partitioned table tbl1 and the partitioned table tbl2 cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT) TABLEGROUP = tblgroup3;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup3
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES ('01'),
       PARTITION p1 VALUES ('02')
       );
  ORA-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• Create a table group named tblgroup4 with the SHARDING attribute PARTITION. The partitioned tables tbl1 and tbl2 have different partitioning values and cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02'),
         PARTITION p2 VALUES ('03')
         );
  ORA-00600: internal error code, arguments: -4179, partition num not equal, add table to tablegroup not allowed
  

• Create a table group named tblgroup5 with the SHARDING attribute ADAPTIVE, and add partitioned tables tbl1 and tbl2 to the table group. The two tables have the same partitioning method.

  obclient> CREATE TABLEGROUP tblgroup5 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup5
      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 = tblgroup5
      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
  

• Create a table group named tblgroup6 with the SHARDING attribute ADAPTIVE. The non-partitioned table tbl1 and partitioned table tbl2 cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT) TABLEGROUP = tblgroup6;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup6
      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')
        );
  ORA-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• Create a table group named tblgroup7 with the SHARDING attribute ADAPTIVE. Because the partitioned tables tbl1 and tbl2 have the same partitioning type but different subpartitioning types (different numbers of subpartitions), they cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup7 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup7
      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 = tblgroup7
      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')
        );
  ORA-00600: internal error code, arguments: -4179, subpartition num not matched, add table to tablegroup not allowed
  

References

• ALTER TABLEGROUP

• DROP TABLEGROUP

• Manage tables in a table group

• Query information about a table group