CREATE TABLEGROUP

Purpose

This statement is used to create a table group.

A table group is mainly used to create a group of tables with the same distribution characteristics. When tables with the same distribution characteristics are joined, the join can be performed locally, avoiding cross-node data requests.

Syntax

CREATE TABLEGROUP tablegroup_name [ tablegroup_options ]

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

Parameters

Examples

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

  obclient> CREATE TABLEGROUP myTableGroup1;
  
  obclient> CREATE TABLE MYT1 (c1 NUMBER, c2 NUMBER) TABLEGROUP = myTableGroup1;
  
  obclient> CREATE TABLE MYT2 (c1 NUMBER, c2 NUMBER) TABLEGROUP = myTableGroup1;
  

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

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

• Create a table group named tblgroup2 with the SHARDING attribute set to PARTITION, and add the partitioned tables tbl4 and tbl5 to the table group, which have consistent partitioning.

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  
  obclient> CREATE TABLE tbl4 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
  
  obclient> CREATE TABLE tbl5 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES ('01'),
       PARTITION p1 VALUES ('02')
       );
  

• Create a table group named tblgroup3 with the SHARDING attribute set to PARTITION, and add the non-partitioned table tbl6 and the partitioned table tbl7 to the table group. The two tables cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  
  obclient> CREATE TABLE tbl6 (col INT) TABLEGROUP = tblgroup3;
  
  obclient> CREATE TABLE tbl7 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup3
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES ('01'),
       PARTITION p1 VALUES ('02')
       );
  

  Expected return result:

  OBE-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 set to PARTITION, and add the partitioned tables tbl8 and tbl9 to the table group. The two tables cannot be added to the table group at the same time because their partition values are inconsistent.

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'PARTITION';
  
  obclient> CREATE TABLE tbl8 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
  
  obclient> CREATE TABLE tbl9 (col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02'),
         PARTITION p2 VALUES ('03')
         );
  

  Expected return result:

  OBE-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 set to ADAPTIVE, and add the partitioned tables tbl10 and tbl11 to the table group, which have consistent partitioning.

  obclient> CREATE TABLEGROUP tblgroup5 SHARDING = 'ADAPTIVE';
  
  obclient> CREATE TABLE tbl10(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')
        );
  
  obclient> CREATE TABLE tbl11(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')
        );
  

• Create a table group named tblgroup6 with the SHARDING attribute set to ADAPTIVE, and add the non-partitioned table tbl12 and the partitioned table tbl13 to the table group. The two tables cannot be added to the table group at the same time.

  obclient> CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE';
  
  obclient> CREATE TABLE tbl12 (col INT) TABLEGROUP = tblgroup6;
  
  obclient> CREATE TABLE tbl13(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')
        );
  
  Expected return result:
  
  

  OBE-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 set to ADAPTIVE, and add the partitioned tables tbl14 and tbl15 to the table group. The two tables cannot be added to the table group at the same time because their primary partitions have the same partitioning type but their subpartitions have different partitioning types (different number of subpartitions).

  obclient> CREATE TABLEGROUP tblgroup7 SHARDING = 'ADAPTIVE';
  
  obclient> CREATE TABLE tbl14(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')
        );
  
  obclient> CREATE TABLE tbl15(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')
        );
  

  Expected return result:

  OBE-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

• View information about a table group