CREATE TABLEGROUP|V4.4.2| docs|Distributed Database

CREATE TABLEGROUP

Last Updated：2026-03-06 07:02:42 Updated

Purpose

This statement is used to create a table group.

A table group is mainly used to create a group of tables with the same distribution characteristics. When tables with the same distribution characteristics are joined, the join can be performed locally, avoiding 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. It can be up to 64 characters long and can contain only uppercase and lowercase letters, digits, and underscores. It must start with a letter or underscore and cannot be a keyword of OceanBase Database. If the table group with the specified name already exists and the IF NOT EXISTS clause is not specified, an error will be returned.

SHARDING

Parameter	Description
tablegroup_name	The name of the table group. It can be up to 64 characters long and can contain only uppercase and lowercase letters, digits, and underscores. It must start with a letter or underscore and cannot be a keyword of OceanBase Database. If the table group with the specified name already exists and the `IF NOT EXISTS` clause is not specified, an error will be returned.
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 as follows: `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 those of all tables in the table group. The partitioning type, number of partitions, and partition values must be the same. `ADAPTIVE`: The primary partitions and subpartitions of the tables added to the table group must be consistent with those of all tables in the table group. The partitioning type, number of partitions, and partition values must be the same. The tables added to the table group must be either all primary-partitioned tables or all subpartitioned tables. Consistent partitioning includes the following aspects: Same partitioning type (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 those 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 as follows:

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 those of all tables in the table group. The partitioning type, number of partitions, and partition values must be the same.
ADAPTIVE: The primary partitions and subpartitions of the tables added to the table group must be consistent with those of all tables in the table group. The partitioning type, number of partitions, and partition values must be the same. The tables added to the table group must be either all primary-partitioned tables or all subpartitioned tables.

Consistent partitioning includes the following aspects:

Same partitioning type (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 those for primary partitions.

Examples

Create a table group named myTableGroup1 and add the 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, the primary-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 the SHARDING attribute set to PARTITION, and add the partitioned tables tbl4 and tbl5 to the table group, which have consistent partitioning.

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 add the non-partitioned table tbl6 and the partitioned table tbl7 to the table group. The two tables cannot be added to the 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 the SHARDING attribute set to PARTITION, and add the partitioned tables tbl8 and tbl9 to the table group. The two tables cannot be added to the table group at the same time because their partition values are inconsistent.

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 the table group, which have consistent partitioning.

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 add the non-partitioned table tbl12 and the partitioned table tbl13 to the table group. The two tables cannot be added to the table group at the same time.

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 add the partitioned tables tbl14 and tbl15 to the table group. The two tables cannot be added to the table group at the same time because their primary partitions have the same partitioning type but their subpartitions have different partitioning 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