After creating a table group, you can modify its SHARDING attribute based on business requirements.
Limitations
During the upgrade of OceanBase AI Database, using the ALTER TABLEGROUP statement to modify the SHARDING attribute of a table group is prohibited.
Modification rules
When modifying the SHARDING attribute of a table group, you must first check whether any tables exist in the table group and the partition information of the tables. For more information about how to view tables in a table group, see View table group information.
Based on the query results, if no tables exist in the table group, you can freely modify its SHARDING attribute. If tables exist in the table group, follow these modification rules:
When changing the
SHARDINGattribute of a table group toNONE, you do not need to consider the partition definitions of the tables in the table group and can modify it directly.When changing the
SHARDINGattribute of a table group toPARTITION:If all tables in the table group are non-partitioned tables, you can modify it directly.
If the table group contains both non-partitioned and partitioned tables, you cannot modify it.
If the table group contains only partitioned tables or only subpartitioned tables, the partition types, numbers, and values of the partitions of all tables in the table group must be the same; otherwise, you cannot modify it.
When changing the
SHARDINGattribute of a table group toADAPTIVE:If all tables in the table group are non-partitioned tables, you can modify it directly.
If the table group contains both non-partitioned and partitioned tables, you cannot modify it.
If the table group contains both partitioned and subpartitioned tables, you cannot modify it.
If all tables in the table group are partitioned tables, the partition types, numbers, and values of the partitions of all partitioned tables must be the same.
If all tables in the table group are subpartitioned tables, the partition types, numbers, and values of the partitions and subpartitions of all subpartitioned tables must be the same.
When changing the
SHARDINGattribute of a table group toSUBPARTITION:All tables in the table group must be subpartitioned tables.
The partitioning and subpartitioning methods of all tables must be exactly the same.
This check is not required if no tables exist in the table group.
Modify table group attributes
The SQL statement for modifying the SHARDING attribute of a table group is as follows:
ALTER TABLEGROUP tablegroup_name tablegroup_option;
tablegroup_option:
SHARDING = 'NONE'
SHARDING = 'PARTITION'
SHARDING = 'ADAPTIVE'
SHARDING = 'SUBPARTITION'
Instructions for using the statement:
To execute this statement, ensure the current user has the global
ALTERprivilege.tablegroup_name: The name of the table group to be modified.SHARDING: The attribute of the table group. When modifying the table group attribute, you must follow the modification rules.
The following example shows how to modify the attribute of a table group:
Create a table group named
tblgroup1with itsSHARDINGattribute set toPARTITION.CREATE TABLEGROUP tblgroup1 SHARDING = 'PARTITION';Create two partitioned tables,
tbl1andtbl2, with the same partition definition, and add them to thetblgroup1table group.CREATE TABLE tbl1 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1 PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) );CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1 PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) );Change the
SHARDINGattribute of thetblgroup1table group toADAPTIVE.ALTER TABLEGROUP tblgroup1 SHARDING = "ADAPTIVE";Query the attribute of the table group.
SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';The result is as follows:
+-----------------+------------+---------------+----------+ | Tablegroup_name | Table_name | Database_name | Sharding | +-----------------+------------+---------------+----------+ | tblgroup1 | tbl1 | test | ADAPTIVE | | tblgroup1 | tbl2 | test | ADAPTIVE | +-----------------+------------+---------------+----------+ 2 rows in setThe tables
tbl1andtbl2within the table group are both partitioned tables. They have the same partition type, number of partitions, and partition values, which complies with the modification rules. TheSHARDINGattribute is successfully modified.