CREATE TABLEGROUP

Purpose

This statement is used to create a table group.

Table groups are primarily used to create a set of tables with identical distribution characteristics. Tables with the same distribution can be joined locally, avoiding cross-node data requests.

Limitations and considerations

• During the upgrade of OceanBase Database, creating table groups is prohibited.

• For V5.x versions, starting from V5.0.1, the distribution of partitions within a table group is determined jointly by the SHARDING and SCOPE attributes. The current version only supports certain combinations of the SHARDING and SCOPE attributes. Specifying an unsupported combination will result in an error. Supported combinations include:

  • SHARDING = 'NONE' + SCOPE = 'SERVER'
  • SHARDING = 'NONE' + SCOPE = 'ZONE'
  • SHARDING = 'NONE' + SCOPE = 'CLUSTER'
  • SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
  • SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'
  • SHARDING = 'SUBPARTITION' + SCOPE = 'CLUSTER'

Limitations and considerations

• Creating table groups is prohibited during OceanBase Database upgrades.

• For V5.x versions, starting from V5.0.1, the partition distribution within a table group is determined by both SHARDING and SCOPE. Current versions only support certain combinations of the SHARDING and SCOPE attributes. Specifying unsupported combinations will result in an error. Supported combinations include:

  • SHARDING = 'NONE' + SCOPE = 'SERVER'
  • SHARDING = 'NONE' + SCOPE = 'ZONE'
  • SHARDING = 'NONE' + SCOPE = 'CLUSTER'
  • SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
  • SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'

Syntax

CREATE TABLEGROUP tablegroup_name
    [sharding_option,]
    [scope_option];

sharding_option:
    SHARDING = '{NONE | PARTITION | ADAPTIVE | SUBPARTITION}'

scope_option:
    SCOPE = '{SERVER | ZONE | CLUSTER}'

Parameters

sharding_option

• NONE: No restrictions are placed on tables that can be added to the table group.

• PARTITION: Indicates the partition of the table to be added to the table group. It must have the same partitioning method as all tables in the table group, including the partition type, number of partitions, and partition values.

• ADAPTIVE: Indicates that the partitions of the tables added to the table group, including both primary and secondary partitions, must be consistent with those of all other tables in the group in terms of partition type, number of partitions, and partition values. The tables added to the table group must all be of the same type (either all primary partitioned tables or all subpartitioned tables).

• SUBPARTITION: Indicates that the tables added to the table group must be subpartitioned tables. The partitioning methods for the primary partitions of different tables must be the same, and so must the subpartitioning methods, including the partitioning key type, partitioning function, number of partitions, and partition boundary values.

  Note

  For V5.x versions, the SHARDING = 'SUBPARTITION' attribute for table groups is supported starting from V5.0.1.

The same partitioning methods include:

• The partition types are the same (for example, both are HASH+RANGE partitions).
• For HASH partitioning, the number of referenced columns and the number of partitions must be the same.
• For RANGE partitioning, the number of referenced columns, the number of partitions, and the RANGE partitioning points must be the same.

For subpartitions, the requirements are the same as those above, depending on their partition type.

scope_option

• scope_option: Optional. Specifies the SCOPE attribute of the table group. The SCOPE determines the distribution range of all aggregated partition groups within the table group. Valid values:

  Note

  For V5.x versions, the SCOPE attribute for table groups is supported starting from V5.0.1.

  • SERVER: Indicates that the leaders of all partition groups are aggregated on the same node.
  • ZONE: Indicates that the leaders of all partition groups are distributed within the same zone and are scattered across nodes within that zone.
  • CLUSTER: Indicates that the leaders of all partition groups are distributed across nodes in the cluster.

  If the SCOPE attribute is not specified for a table group, it will be automatically set to the corresponding default SCOPE based on its SHARDING attribute. The specific mapping is as follows:

  • When SHARDING = 'NONE', the value of SCOPE is SERVER.
  • When SHARDING = 'PARTITION', the value of SCOPE must be CLUSTER.
  • When SHARDING = 'ADAPTIVE', the value of SCOPE must be CLUSTER.
  • When SHARDING = 'SUBPARTITION', the value of SCOPE must be CLUSTER.

  Notice

  The table groups upgraded from earlier versions also have their SCOPE determined by the same rules.

Examples

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

  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 its SHARDING attribute set to NONE, and add the non-partitioned table tbl1, the 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 its SHARDING attribute set to PARTITION, and add the partitioned tables tbl4 and tbl5 to the table group. Both tables are partitioned in the same way.

  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 its SHARDING attribute set to PARTITION. Non-partitioned table tbl6 and partitioned table tbl7 cannot be added to this 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 its SHARDING attribute set to PARTITION. The partition values of partitioned tables tbl8 and tbl9 are inconsistent, so they cannot be added to the table group at the same time.

  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 value:

  OBE-00600: internal error code, arguments: -4179, partition num not equal, add table to tablegroup not allowed
  

• Create a table group named tblgroup5 with its SHARDING attribute set to ADAPTIVE, and add the partitioned tables tbl10 and tbl11 to the table group. Both tables have the same partitioning method.

  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 its SHARDING attribute set to ADAPTIVE. The non-partitioned table tbl12 and the partitioned table tbl13 cannot be added to the same table group.

  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 value:
  
  ``sql
  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 its SHARDING attribute set to ADAPTIVE. Since partitioned tables tbl14 and tbl15 have the same partition type for their partitions but different subpartition types (and different numbers of subpartitions), they cannot be added to the table group simultaneously.

  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 value:

  OBE-00600: internal error code, arguments: -4179, subpartition num not matched, add table to tablegroup not allowed
  

• Create a table group named tblgroup8 with its SHARDING attribute set to SUBPARTITION. Then, create a subpartitioned table and specify the table group attribute as tblgroup8.

  1. Create the table group tblgroup8.

     obclient> CREATE TABLEGROUP tblgroup8 SHARDING = 'SUBPARTITION';
     

  2. Create a subpartitioned table named t1 and specify the table group attribute as tblgroup8.

     obclient> CREATE TABLE t1 (id INT, c1 INT, c2 INT, PRIMARY KEY (id, c1))
         TABLEGROUP = tblgroup8
         PARTITION BY HASH(id)
             SUBPARTITION BY HASH(c1)
             SUBPARTITIONS 2
             PARTITIONS 3;
     

  3. Create a subpartitioned table named t2 and specify the table group attribute as tblgroup8.

     obclient> CREATE TABLE t2 (id INT, c1 INT, c2 INT, PRIMARY KEY (id, c1))
         TABLEGROUP = tblgroup8
         PARTITION BY HASH(id)
             SUBPARTITION BY HASH(c1)
             SUBPARTITIONS 2
             PARTITIONS 3;
     

References

• ALTER TABLEGROUP

• DROP TABLEGROUP

• Manage tables within a table group

• Query table group information