Modify the SHARDING attribute of a table group

You can modify the SHARDING attribute of a table group based on business adjustments.

Limitations

Do not execute the ALTER TABLEGROUP statement to modify the SHARDING attribute during the upgrade of OceanBase Database.

Modification rules

Before you modify the SHARDING attribute of a table group, check whether the table group contains tables and the partition information of the tables. For more information, see Query table group information.

If the table group does not contain tables, you can modify the SHARDING attribute of the table group as needed. If the table group contains tables, the modification is subject to the following rules:

• If you want to set the SHARDING attribute of the table group to NONE, you can perform the modification without considering the partition definitions of the tables in the table group.

• If you want to set the SHARDING attribute of the table group to PARTITION, the partitioned tables must meet the following requirements:

  • If all tables in the table group are non-partitioned tables, the modification is allowed.

  • If the table group contains both non-partitioned tables and partitioned tables, the modification is not allowed.

  • If the table group contains only partitioned tables, the partition type, number of partitions, and partition values must be the same for all tables in the table group.

• If you want to set the SHARDING attribute of the table group to ADAPTIVE, the partitioned tables must meet the following requirements:

  • If all tables in the table group are non-partitioned tables, the modification is allowed.

  • If the table group contains both non-partitioned tables and partitioned tables, the modification is not allowed.

  • If the table group contains both partitioned tables and subpartitioned tables, the modification is not allowed.

  • If all tables in the table group are partitioned tables, the partition type, number of partitions, and partition values must be the same for all tables in the table group.

  • If all tables in the table group are subpartitioned tables, the partition type, number of partitions, and partition values must be the same for all tables in the table group.

Modify the table group attribute

The SQL statement to modify the SHARDING attribute of a table group is as follows:

ALTER TABLEGROUP tablegroup_name tablegroup_option;

tablegroup_option:
SHARDING = 'NONE'
SHARDING = 'PARTITION'
SHARDING = 'ADAPTIVE'

The statement is described as follows:

• Before you execute the statement, make sure that you have the global ALTER privilege.

• tablegroup_name: the name of the table group whose attribute is to be modified.

• SHARDING: the table group attribute. When you modify the table group attribute, you must follow the modification rules.

Here is an example of modifying the table group attribute:

1. Create a table group named tblgroup1 with the SHARDING attribute.

   CREATE TABLEGROUP tblgroup1 SHARDING = 'PARTITION'; 
   

2. Create two partitioned tables named tbl1 and tbl2 with the same partition definition and add them to the tblgroup1 table 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)
   );
   

3. Change the SHARDING attribute of the tblgroup1 table group to ADAPTIVE.

   ALTER TABLEGROUP tblgroup1 SHARDING = "ADAPTIVE";
   

4. 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 set
   

   The tbl1 and tbl2 tables in the table group are all partitioned tables. The partition types, number of partitions, and partition values are the same for the two tables. Therefore, the modification to the SHARDING attribute complies with the modification rules, and the modification succeeds.

References

• Overview of table groups

• Create a table group

• Query information about a table group

• Add tables to a table group

• Manage tables in a table group

• Delete a table group