All Products
Search
Document Center

CREATE TABLEGROUP

Last Updated: Jun 18, 2021

Description

This statement creates a table group.

Note

Only the tenant administrator can create table groups.

Syntax

CREATE TABLEGROUP [IF NOT EXISTS] tablegroupname [opt_tablegroup_option_list] [opt_tg_partition_option]

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

tablegroup_option:
LOCALITY [=] locality_name
| PRIMARY_ZONE [=] primary_zone_name

opt_tg_partition_option:
PARTITION BY 
 HASH COLUMN_NUM [tg_subpartition_option] PARTITIONS INTNUM
| RANGE COLUMNS COLUMN_NUM [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST COLUMNS COLUMN_NUM [tg_subpartition_option] {PARTITION partition_name VALUES in list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY 
RANGE COLUMN_NUM SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| HASH COLUMN_NUM [SUBPARTITIONS INTNUM]
| LIST COLUMN_NUM SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES in list_partition_expr, ...}

Parameter description

Parameter

Description

tablegroupname

The name of the table group. It must be up to 64 characters in length and can contain only letters, digits, and underscores (_). The name must start with a letter or an underscore (_) and cannot be a keyword that is reserved for ApsaraDB for OceanBase.

If the specified table group name is already used and IF NOT EXISTS is not specified, an error occurs.

opt_tablegroup_option_list

The partitioning method, locality, and primary zone of the table group must be exactly consistent with those of the tables in the group.

You cannot independently change an item of table information in the table group. You can perform only batch operations on the table group.

Same locality: The types, quantities, and locations for replicas must be exactly consistent.

Same primary zone: The locations and the precedences of leaders must be exactly consistent.

Same partitioning method:

  • The partitioning types must be the same. For example, hash partitioning and range partitioning are used for each table.

  • If hash partitioning is used, the numbers of referenced columns must be the same and the numbers of partitions must be same.

  • If range partitioning is used, the number of referenced columns must be the same and the number of partitions must be the same. In addition, the range split points must be the same.

  • The subpartition requirements must be consistent with the preceding requirements based on the partitioning types.

opt_tg_partition_option

Specifies the partitioning rule for the table group. This rule is the same as the partitioning method that is used by CREATE TABLE.

The table group does not have the specific column definition. Therefore, you do not need to write specific columns for HASH, RANGE, and LIST, and you need to specify only the number of columns (COLUMN_NUM).

Examples

  • Create a table group that is named myTableGroup1.

OceanBase(admin@test)> CREATE TABLEGROUP myTableGroup1;
Query OK, 0 rows affected (0.07 sec)

OceanBase(admin@test)> create table myt1 (c1 int, c2 int ) tablegroup = myTableGroup1;
Query OK, 0 rows affected (0.28 sec)

OceanBase(admin@test)> create table myt2 (c1 int, c2 int ) tablegroup = myTableGroup1;
Query OK, 0 rows affected (0.26 sec)
  • Create the table group tgh whose partitioning method is hash partitioning. Create the ttgh table whose partitioning method is hash partitioning. The number of partitions for the table group tgh is the same as that for the ttgh table.

OceanBase(admin@test)> create tablegroup tgh locality='F,R{ALL_SERVER}@z1' partition by hash partitions 10;
Query OK, 0 rows affected (0.09 sec)

OceanBase(admin@test)> create table ttgh(c1 int, c2 int) locality='F,R{ALL_SERVER}@z1' partition by hash(c1) partitions 10;
Query OK, 0 rows affected (0.55 sec)

OceanBase(admin@test)> create table ttgh2(c1 int, c2 int) locality='F,R{ALL_SERVER}@z1' partition by hash(c2) partitions 10;
Query OK, 0 rows affected (0.39 sec)