Statements for table group management

This topic describes the management statements for non-partitioned table groups.

For information about the management statements for partitioned table groups, see Create a partitioned table group.

Create a table group

SQL syntax:

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

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

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

• tablegroupname: the table group name, which can be a maximum of 64 characters in length. The name can contain letters, digits, and underscores (_), must start with a letter or an underscore (_), and cannot contain an OceanBase keyword. If the table group name already exists and IF NOT EXISTS is not specified, an error is returned.

• opt_tablegroup_option_list: the partitioning method of the table group. The locality and primary zone information of the table group must be consistent with that of the tables in the table group. You cannot modify a single item in a table. You must perform batch operations on the entire table group.

Example for creating a table group named grp1:

obclient> CREATE TABLEGROUP grp1;

Add a table to a table group

Example for adding the t1 table to the grp1 table group:

obclient>  ALTER TABLE t1 SET TABLEGROUP grp1;

Remove a table from a table group

Example for removing the t1 table from its table group:

obclient> ALTER TABLE t1 SET TABLEGROUP '';

Move a table to another table group

Example for moving the t1 table from the grp1 table group to the grp2 table group:

obclient> ALTER TABLE t1 SET TABLEGROUP grp2;

Drop a table group

You can drop a table group that is not referenced by the TABLEGROUP property of a table.

SQL syntax:

DROP TABLEGROUP [IF EXISTS] tablegroupname

• tablegroupname: the table group name. If the table group name does not exist and IF EXISTS is not specified, an error is returned.

Example for dropping the grp1 table group:

obclient> DROP TABLEGROUP grp1;

Before you drop a table group, you can execute the following statement to check whether the table group contains tables. If yes, remove the tables from the table group.

obclient> SHOW TABLEGROUPS;
+-----------------+-------------------------------+--------------------+
| Tablegroup_name | Table_name                    | Database_name      |
+-----------------+-------------------------------+--------------------+
| grp1            | t1                            | oceanbase          |
+-----------------+-------------------------------+--------------------+

Modify the locality and primary zone of a table group

SQL syntax:

ALTER TABLEGROUP tablegroupname alter_tablegroup_actions;

alter_tablegroup_actions:
alter_tablegroup_action
| alter_tablegroup_action, alter_tablegroup_action

alter_tablegroup_action:
SET LOCALITY [=] locality_name
|SET PRIMARY_ZONE [=] primary_zone_name

• modify_tg_partition_info: modifies the partitioning rules of the table group.

• LOCALITY locality_name: the locality of the table group.

• PRIMARY_ZONE primary_zone_name: the primary zone of the table group.

Modify locality

Example for changing the locality of all tables in the grp1 table group to F@z1,F@z3:

obclient> ALTER TABLEGROUP grp1 SET locality = 'F@z1,F@z3';

Change the primary zone

Example for changing the primary zone of all tables in the grp1 table group to z1,z2:

obclient> ALTER TABLEGROUP grp1 SET primary_zone = 'z1,z2';