All Products
Search
Document Center

CREATE TABLEGROUP

Last Updated: Jun 18, 2021

Description

You can execute the CREATE TABLEGROUP statement to create a table group.

Note

Only an administrator under a tenant can create a table group.

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 
 KEY COLUMN_NUM [tg_subpartition_option] PARTITIONS INTNUM
| HASH [tg_subpartition_option] PARTITIONS INTNUM
| RANGE [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| RANGE COLUMNS COLUMN_NUM [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST [tg_subpartition_option] {PARTITION partition_name VALUES in list_partition_expr, ...}
| LIST COLUMNS COLUMN_NUM [tg_subpartition_option] {PARTITION partition_name VALUES in list_partition_expr, ...}

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

Parameters

Parameter

Description

tablegroupname

A table group name must be up to 64 characters in length and can contain 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 tenant name is used and the IF NOT EXISTS option is not specified, the system returns an error.

opt_tablegroup_option_list

The partition mode, locality, and primary zone of a table group must be the same as those of the tables in the table group.

You cannot modify a single table record in a table group. You can only modify a table group to modify multiple table records at a time.

Tables in a table group must be in the same locality. The type, number, and locality of replicas must be the same.

Tables in a table group must be in the same primary zone and have the same replica leader. The priority of the replicas is the same.

Tables in a table group are partitioned in the same way.

  • Specify the same partitioning type for all tables. For example, partition all tables in hash-range partitioning mode.

  • To use key partitioning, reference the same number of columns in each table, and ensure that each table has the same number of partitions.

  • To use hash partitioning, ensure that each table has the same number of partitions.

  • To use range columns partitioning, reference the same number of columns in each table, and ensure that each table has the same number of partitions and uses the same partitioning column values.

  • To use range partitioning, ensure that each table has the same number of partitions and uses the same partitioning column values.

  • For sub-partitioning, follow the preceding rules based on the partitioning type.

opt_tg_partition_option

The partitioning rule of the table group. Use the partitioning rule that you specify when you create a table.

A table group has no columns. Therefore, you do not need to specify column names for KEY, RANGE COLUMNS, and LIST COLUMNS. You only need to specify COLUMN_NUM.

Examples

  • Create table group 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 table group tgh and table ttgh. tgh and ttgh use hash partitioning and have the same number of partitions.
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)