Purpose
This statement is used to create a table group.
Table groups are primarily used to create a set of tables with identical distribution characteristics. Tables with the same distribution can be joined locally, avoiding cross-node data requests.
To create a table group, the current user must have the global CREATE (*.*) privilege.
Syntax
CREATE TABLEGROUP [ IF NOT EXISTS ] tablegroup_name [tablegroup_option_list];
tablegroup_option_list:
tablegroup_option [, tablegroup_option ...]
tablegroup_option:
SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE' | 'SUBPARTITION'
Parameters
Parameters |
Description |
|---|---|
| tablegroup_name | The table group name must be at most 64 characters long. It can contain uppercase and lowercase English letters, digits, and underscores. It must start with a letter or an underscore, and cannot be a keyword of OceanBase AI Database. If the table group name to be created already exists and no specific... IF NOT EXISTS, an error will occur. |
| IF NOT EXISTS | If the table group name to be created already exists, and you specify IF NOT EXISTS, no error will be reported, and no new table group will be created. |
| SHARDING | Attributes of the table group. If the SHARDING attribute is not specified, a table group with the ADAPTIVE attribute is created by default. SHARDING Valid values:
|
Examples
Create a table group named
myTableGroup1and add the non-partitioned tablesmyt1andmyt2to it.obclient> CREATE TABLEGROUP myTableGroup1; obclient> CREATE TABLE myt1 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1; obclient> CREATE TABLE myt2 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;Create a table group named
order_tg_nonewith itsSHARDINGattribute set toNONE, and add the non-partitioned tableuser_table, the partitioned tableorder_table, and the subpartitioned tableorder_item_tableto this table group.obclient> CREATE TABLEGROUP order_tg_none SHARDING = 'NONE'; obclient> CREATE TABLE user_table (col INT) TABLEGROUP = order_tg_none; obclient> CREATE TABLE order_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = order_tg_none PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) ); obclient> CREATE TABLE order_item_table(col1 INT,col2 varchar(50)) TABLEGROUP = order_tg_none PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02'), SUBPARTITION mp2 VALUES in ('03')), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02'), SUBPARTITION mp5 VALUES in ('03')) );Create a table group named
product_tg_partitionwith itsSHARDINGattribute set toPARTITION. Add the partitioned tablesproduct_tableandcategory_tableto this table group, ensuring they use the same partitioning method.obclient> CREATE TABLEGROUP product_tg_partition SHARDING = 'PARTITION'; obclient> CREATE TABLE product_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) ); obclient> CREATE TABLE category_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) );Create a table group named
mixed_tg_partitionwith itsSHARDINGattribute set toPARTITION. The non-partitioned tableuser_table_npand the partitioned tableorder_table_pcannot be added to this table group at the same time.obclient> CREATE TABLEGROUP mixed_tg_partition SHARDING = 'PARTITION'; obclient> CREATE TABLE user_table_np (col INT) TABLEGROUP = mixed_tg_partition; obclient> CREATE TABLE order_table_p (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_partition PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) ); ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowedCreate a table group named
product_tg_partition2with itsSHARDINGattribute set toPARTITION. The partition values of the partitioned tablesproduct_table2andcategory_table2are inconsistent, so they cannot be added to the table group simultaneously.obclient> CREATE TABLEGROUP product_tg_partition2 SHARDING = 'PARTITION'; obclient> CREATE TABLE product_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2 PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) ); obclient> CREATE TABLE category_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2 PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 7), PARTITION p2 VALUES IN (DEFAULT) ); ERROR 4179 (HY000): list_part partition value not equal, add table to tablegroup not allowedCreate a table group named
order_tg_adaptivewith itsSHARDINGattribute set toADAPTIVE, and add the partitioned tablesorder_detail_tableandorder_item_detail_tableto this table group. Both tables have the same partitioning method.obclient> CREATE TABLEGROUP order_tg_adaptive SHARDING = 'ADAPTIVE'; obclient> CREATE TABLE order_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02'), SUBPARTITION mp2 VALUES in ('03')), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02'), SUBPARTITION mp5 VALUES in ('03')) ); obclient> CREATE TABLE order_item_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02'), SUBPARTITION mp2 VALUES in ('03')), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02'), SUBPARTITION mp5 VALUES in ('03')) );Create a table group named
mixed_tg_adaptivewith itsSHARDINGattribute set toADAPTIVE. The non-partitioned tableuser_table_np2and the partitioned tableorder_detail_table2cannot be added to the same table group.obclient> CREATE TABLEGROUP mixed_tg_adaptive SHARDING = 'ADAPTIVE'; obclient> CREATE TABLE user_table_np2 (col int) TABLEGROUP = mixed_tg_adaptive; obclient> CREATE TABLE order_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_adaptive PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02'), SUBPARTITION mp2 VALUES in ('03') ), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02'), SUBPARTITION mp5 VALUES in ('03')) ); ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowedCreate a table group named
order_tg_adaptive2with itsSHARDINGattribute set toADAPTIVE. Since the partitioned tablesorder_detail_table3andorder_item_detail_table2have the same partition type for their partitions but different subpartition types (and different numbers of subpartitions), they cannot be added to the table group together.obclient> CREATE TABLEGROUP order_tg_adaptive2 SHARDING = 'ADAPTIVE'; obclient> CREATE TABLE order_detail_table3(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2 PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02'), SUBPARTITION mp2 VALUES in ('03') ), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02'), SUBPARTITION mp5 VALUES in ('03')) ); obclient> CREATE TABLE order_item_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2 PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES in (01) (SUBPARTITION mp0 VALUES in ('01'), SUBPARTITION mp1 VALUES in ('02')), PARTITION p1 VALUES in (02) (SUBPARTITION mp3 VALUES in ('01'), SUBPARTITION mp4 VALUES in ('02')) ); ERROR 4179 (HY000): subpartition num not matched, add table to tablegroup not allowedCreate a table group named
tblgroup8with itsSHARDINGattribute set toSUBPARTITION. Then, create a subpartitioned table and specify the table group attribute astblgroup8.Create a table group named
tblgroup8.obclient> CREATE TABLEGROUP tblgroup8 SHARDING = 'SUBPARTITION';Create a subpartitioned table named
t1and specify the table group attribute astblgroup8.obclient> CREATE TABLE t1 (id INT, c1 INT, c2 INT, PRIMARY KEY (id, c1)) TABLEGROUP = tblgroup8 PARTITION BY HASH(id) SUBPARTITION BY HASH(c1) SUBPARTITIONS 2 PARTITIONS 3;Create a subpartitioned table named
t2and specify the table group attribute astblgroup8.obclient> CREATE TABLE t2 (id INT, c1 INT, c2 INT, PRIMARY KEY (id, c1)) TABLEGROUP = tblgroup8 PARTITION BY HASH(id) SUBPARTITION BY HASH(c1) SUBPARTITIONS 2 PARTITIONS 3;