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.

Rules

Before you modify the SHARDING attribute of a table group, you must query whether the table group contains tables and partitions. 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 arbitrarily. If the table group contains tables, the following rules apply:

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

• If you want to set the SHARDING attribute to PARTITION, the following rules apply:

  • If all tables in the table group are non-partitioned tables, you can modify it.

  • If the table group contains both non-partitioned tables and partitioned tables, you cannot modify it.

  • If the table group contains only partitioned tables, all tables must be at the same partition level, and the partition types, number of partitions, and partition values must be the same.

• If you want to set the SHARDING attribute to ADAPTIVE, the following rules apply:

  • If all tables in the table group are non-partitioned tables, you can modify it.

  • If the table group contains both non-partitioned tables and partitioned tables, you cannot modify it.

  • If the table group contains both tables partitioned at the first level and tables partitioned at the second level, you cannot modify it.

  • If all tables in the table group are tables partitioned at the first level, the partition types, number of partitions, and partition values must be the same for all first-level partitioned tables.

  • If all tables in the table group are tables partitioned at the second level, the partition types, number of partitions, and partition values must be the same for all first-level and second-level partitioned tables.

Modify the SHARDING attribute of a table group

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 this statement, make sure that you have the global ALTER privilege.

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

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

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 partitions 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 SHARDING attribute of the tblgroup1 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, and they have the same partition types, number of partitions, and partition values. The modification of the SHARDING attribute complies with the rules.

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