Modify the SHARDING attribute of a table group

After you create a table group, you can modify the SHARDING attribute of the table group based on business adjustments.

Limitations

Do not use 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, you must check whether the table group contains tables and the partitioning information of the tables. For more information, see View table group information.

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

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

• If you want to set the SHARDING attribute to PARTITION, the partitioning definitions 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 at the first or second level, all first-level or second-level partitions of the tables in the table group must have the same partitioning type, the same number of partitions, and the same partition values. Otherwise, the modification is not allowed.

• If you want to set the SHARDING attribute to ADAPTIVE, the partitioning definitions 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 first-level partitioned tables and second-level partitioned tables, the modification is not allowed.

  • If all tables in the table group are first-level partitioned tables, all first-level partitions of the tables in the table group must have the same partitioning type, the same number of partitions, and the same partition values. Otherwise, the modification is not allowed.

  • If all tables in the table group are second-level partitioned tables, all first-level and second-level partitions of the tables in the table group must have the same partitioning type, the same number of partitions, and the same partition values. Otherwise, the modification is not allowed.

Modify the table group attribute

You can execute the following statement to modify the SHARDING attribute of a table group:

ALTER TABLEGROUP tablegroup_name tablegroup_option;

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

Note:

• You must have the global ALTER privilege to execute this statement.

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

• SHARDING: the table group attribute. The modification is subject to the rules described in this topic.

Example:

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

   CREATE TABLEGROUP tblgroup1 SHARDING = 'PARTITION'; 
   

2. Create two partitioned tables named tbl1 and tbl2 with the same partitioning definitions, 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. View 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 first-level partitioned tables, and they have the same partitioning type, the same number of partitions, and the same partition values. Therefore, the modification is allowed.

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

• Drop a table group