CREATE TABLEGROUP|V4.3.5| docs|Distributed Database

CREATE TABLEGROUP

Last Updated：2026-02-03 08:48:37 Updated

Purpose

This statement is used to create a table group.

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

Syntax

CREATE TABLEGROUP tablegroup_name [ tablegroup_options ]

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

Parameters

Parameter Description

tablegroup_name The name of the table group, which can be up to 64 characters long. It must start with a letter or underscore and can only contain uppercase and lowercase letters, numbers, and underscores. It cannot be a keyword of OceanBase Database. If a table group with the same name already exists and the IF NOT EXISTS clause is not specified, an error will occur.

SHARDING

Parameter	Description
tablegroup_name	The name of the table group, which can be up to 64 characters long. It must start with a letter or underscore and can only contain uppercase and lowercase letters, numbers, and underscores. It cannot be a keyword of OceanBase Database. If a table group with the same name already exists and the `IF NOT EXISTS` clause is not specified, an error will occur.
SHARDING	The attribute of the table group. If the `SHARDING` attribute is not specified, the default value is `ADAPTIVE`. The value of `SHARDING` can be one of the following: `NONE`: No restrictions are imposed on the tables added to the table group. `PARTITION`: The primary partitions of the tables added to the table group must be consistent with the primary partitions of all tables in the table group, including the partition type, number of partitions, and partition values. `ADAPTIVE`: The primary and subpartitions of the tables added to the table group must be consistent with the primary and subpartitions of all tables in the table group, including the partition type, number of partitions, and partition values. The tables added to the table group must be of the same type (either all primary partitioned tables or all subpartitioned tables). Consistent partitioning includes: The partitioning type is the same (for example, both are Hash+Range partitioned). If the partitioning type is Hash, the number of referenced columns and the number of partitions must be the same. If the partitioning type is Range, the number of referenced columns, the number of partitions, and the Range split points must be the same. For subpartitions, the requirements are the same as for primary partitions.

The attribute of the table group. If the SHARDING attribute is not specified, the default value is ADAPTIVE. The value of SHARDING can be one of the following:

NONE: No restrictions are imposed on the tables added to the table group.
PARTITION: The primary partitions of the tables added to the table group must be consistent with the primary partitions of all tables in the table group, including the partition type, number of partitions, and partition values.
ADAPTIVE: The primary and subpartitions of the tables added to the table group must be consistent with the primary and subpartitions of all tables in the table group, including the partition type, number of partitions, and partition values. The tables added to the table group must be of the same type (either all primary partitioned tables or all subpartitioned tables).

Consistent partitioning includes:

The partitioning type is the same (for example, both are Hash+Range partitioned).
If the partitioning type is Hash, the number of referenced columns and the number of partitions must be the same.
If the partitioning type is Range, the number of referenced columns, the number of partitions, and the Range split points must be the same.

For subpartitions, the requirements are the same as for primary partitions.

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 the SHARDING attribute set to NONE, and add the non-partitioned table tbl1, the primary partitioned table tbl2, and the subpartitioned table tbl3 to it.

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 it, ensuring their partitioning methods are consistent.

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 attempt to add the non-partitioned table tbl6 and the partitioned table tbl7 to it.

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 attempt to add the partitioned tables tbl8 and tbl9 to it, which have inconsistent partition values.

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 it, ensuring their partitioning methods are consistent.

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 attempt to add the non-partitioned table tbl12 and the partitioned table tbl13 to it.

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 attempt to add the partitioned tables tbl14 and tbl15 to it. The primary partitions of tbl14 and tbl15 have the same partition type, but the subpartitions have different 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

CREATE TABLEGROUP

Purpose

Syntax

Parameters

Examples

References