CREATE TABLEGROUP|V4.3.1| docs|Distributed Database

CREATE TABLEGROUP

Last Updated：2024-06-28 05:30:30 Updated

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.

To create a table group, you must have the global CREATE privilege.

Syntax

CREATE TABLEGROUP tablegroup_name [tablegroup_option];

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

Parameters

Parameter Description

tablegroup_name The table group name. It can contain up to 64 characters in length and contain uppercase and lowercase letters, digits, and underscores (_). It must start with a letter or an underscore (_), and cannot contain keywords of OceanBase Database. If the name of the created table group already exists and IF NOT EXISTS is not specified, an error is returned.

SHARDING

Parameter	Description
tablegroup_name	The table group name. It can contain up to 64 characters in length and contain uppercase and lowercase letters, digits, and underscores (`_`). It must start with a letter or an underscore (`_`), and cannot contain keywords of OceanBase Database. If the name of the created table group already exists and `IF NOT EXISTS` is not specified, an error is returned.
SHARDING	The attribute of the table group. If you do not specify the `SHARDING` attribute, an `ADAPTIVE` table group is created by default. Valid values of `SHARDING`: `NONE`: No limitations are set on tables to be added to a table group. `PARTITION`: The partitioning method of tables to be added to a table group must be the same as that of all tables in the table group in terms of the partitioning type, number of partitions, and partition values. `ADAPTIVE`: The partitioning and subpartitioning methods of tables to be added to a table group must be the same as those of all tables in the table group in terms of the partitioning type, number of partitions, and partition values. All tables in the table group must be of the same partitioning level, that is, either partitioning or subpartitioning. Two tables are considered using the same partitioning method when they meet the following conditions: Have the same partitioning type such as HASH-RANGE partitioning. Have the same number of referenced columns and the same number of partitions. (for HASH-partitioned tables) Have the same number of referenced columns, the same number of partitions, and the same range definitions. (for RANGE-partitioned tables) Subpartitions in the table group must also meet the preceding requirements.

The attribute of the table group. If you do not specify the SHARDING attribute, an ADAPTIVE table group is created by default. Valid values of SHARDING:

NONE: No limitations are set on tables to be added to a table group.
PARTITION: The partitioning method of tables to be added to a table group must be the same as that of all tables in the table group in terms of the partitioning type, number of partitions, and partition values.
ADAPTIVE: The partitioning and subpartitioning methods of tables to be added to a table group must be the same as those of all tables in the table group in terms of the partitioning type, number of partitions, and partition values. All tables in the table group must be of the same partitioning level, that is, either partitioning or subpartitioning.

Two tables are considered using the same partitioning method when they meet the following conditions:

Have the same partitioning type such as HASH-RANGE partitioning.
Have the same number of referenced columns and the same number of partitions. (for HASH-partitioned tables)
Have the same number of referenced columns, the same number of partitions, and the same range definitions. (for RANGE-partitioned tables)

Subpartitions in the table group must also meet the preceding requirements.

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 INT, c2 INT ) TABLEGROUP = myTableGroup1;
Query OK, 0 rows affected

obclient> CREATE TABLE myt2 (c1 INT, c2 INT ) 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 INT) TABLEGROUP = tblgroup1;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
       );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl3(col1 INT,col2 varchar(50)) TABLEGROUP = tblgroup1
    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'))
     );
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 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup2
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
      );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup2
  PARTITION BY LIST(col1)
    (PARTITION p0 VALUES IN (1, 2, 3),
     PARTITION p1 VALUES IN (5, 6),
     PARTITION p2 VALUES IN (DEFAULT)
     );
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 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup3
    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 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 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup4
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
       );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup4
    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 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 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup5
    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'))
      );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup5
    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'))
       );
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 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup6
    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 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 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup7
    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'))
      );
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup7
    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 TABLEGROUP

Purpose

Syntax

Parameters

Examples

References