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.

To create a table group, the current user must have the global CREATE (*.*) privilege.

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'

Syntax

CREATE TABLEGROUP [IF NOT EXISTS] 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 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 table 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 partitions, 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 described 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 located 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, its default SCOPE value is automatically set based on the 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.

  Notice

  For table groups upgraded from earlier versions, the SCOPE is 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 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  obclient> CREATE TABLE myt2 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  

• Create a table group named order_tg_none with its SHARDING attribute set to NONE, and add the non-partitioned table user_table, the partitioned table order_table, and the subpartitioned table order_item_table to this table group.

  obclient> CREATE TABLEGROUP order_tg_none SHARDING = 'NONE';
  obclient> CREATE TABLE user_table (col INT) TABLEGROUP = order_tg_none;
  obclient> CREATE TABLE order_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = order_tg_none
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  obclient> CREATE TABLE order_item_table(col1 INT,col2 varchar(50)) TABLEGROUP = order_tg_none
      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'))
       );
  

• Create a table group named product_tg_partition with its SHARDING attribute set to PARTITION. Add the partitioned tables product_table and category_table to this table group, ensuring they use the same partitioning method.

  obclient> CREATE TABLEGROUP product_tg_partition SHARDING = 'PARTITION';
  obclient> CREATE TABLE product_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
  obclient> CREATE TABLE category_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
       );
  

• Create a table group named mixed_tg_partition with its SHARDING attribute set to PARTITION. The non-partitioned table user_table_np and the partitioned table order_table_p cannot be added to this table group at the same time.

  obclient> CREATE TABLEGROUP mixed_tg_partition SHARDING = 'PARTITION';
  obclient> CREATE TABLE user_table_np (col INT) TABLEGROUP = mixed_tg_partition;
  obclient> CREATE TABLE order_table_p (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_partition
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• Create a table group named product_tg_partition2 with its SHARDING attribute set to PARTITION. The partition values of the partitioned tables product_table2 and category_table2 are inconsistent, so they cannot be added to the table group simultaneously.

  obclient> CREATE TABLEGROUP product_tg_partition2 SHARDING = 'PARTITION';
  obclient> CREATE TABLE product_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  obclient> CREATE TABLE category_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 7),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  ERROR 4179 (HY000): list_part partition value not equal, add table to tablegroup not allowed
  

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

  obclient> CREATE TABLEGROUP order_tg_adaptive SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE order_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive
      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 order_item_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive
      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'))
         );
  

• Create a table group named mixed_tg_adaptive with its SHARDING attribute set to ADAPTIVE. The non-partitioned table user_table_np2 and the partitioned table order_detail_table2 cannot be added to the same table group.

  obclient> CREATE TABLEGROUP mixed_tg_adaptive SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE user_table_np2 (col int) TABLEGROUP = mixed_tg_adaptive;
  obclient> CREATE TABLE order_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_adaptive
      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'))
         );
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• Create a table group named order_tg_adaptive2 with its SHARDING attribute set to ADAPTIVE. Since the partitioned tables order_detail_table3 and order_item_detail_table2 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 together.

  obclient> CREATE TABLEGROUP order_tg_adaptive2 SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE order_detail_table3(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2
      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 order_item_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2
      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'))
         );
  ERROR 4179 (HY000): 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

• View table group information