CREATE TABLEGROUP|V4.3.5| docs|Distributed Database

CREATE TABLEGROUP

Last Updated：2025-11-14 07:33:32 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.

Syntax

CREATE TABLEGROUP tablegroup_name [ tablegroup_options ] 

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 `SHARDING` attribute of the table group. If you do not specify the `SHARDING` attribute, an `ADAPTIVE` table group is created by default. Valid values: `NONE`: No restrictions are imposed on the tables added to the 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 if the tables are HASH-partitioned tables. Have the same number of referenced columns, the same number of partitions, and the same range definitions if the tables are RANGE-partitioned tables. Subpartitions in the table group must also meet the preceding requirements.

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

NONE: No restrictions are imposed on the tables added to the 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 if the tables are HASH-partitioned tables.
Have the same number of referenced columns, the same number of partitions, and the same range definitions if the tables are 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;

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, partitioned table tbl2, and 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 that use the same partitioning method to the table group.

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. The non-partitioned table tbl6 and the partitioned table tbl7 cannot be both added to the table group.

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. The partitioned tables tbl8 and tbl9 have different partitioning key values and therefore cannot be both added to the table group.

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 property set to ADAPTIVE, and add the partitioned tables tbl10 and tbl11 to the table group. Both tables use 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 the SHARDING attribute set to ADAPTIVE. The non-partitioned table tbl2 and the partitioned table tbl3 cannot be both added to the 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 result:

``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 the SHARDING attribute set to ADAPTIVE. The partitioned tables tbl14 and tbl15 have the same partitioning type but different subpartitioning types, namely, different numbers of subpartitions, and therefore cannot be both added to the table group.

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

CREATE TABLEGROUP

Purpose

Syntax

Parameters

Examples

References