Modify the SHARDING attribute of a table group

After you create a table group, you can modify its SHARDING attribute based on business needs.

Limitations

During the upgrade of OceanBase Database, it is prohibited to use the ALTER TABLEGROUP statement to modify the SHARDING attribute of a table group.

Modification rules

Before you modify the SHARDING attribute of a table group, check whether the table group contains tables. If yes, view the partition information of the tables. For more information about how to view tables in a table group, see Query information about a table group.

If the table group does not contain tables, you can modify the SHARDING attribute as needed. If the table group contains tables, modify the SHARDING attribute of the table group based on the following rules:

• You can directly change the value of the SHARDING attribute to NONE without considering the partition definition of tables in the table group.

• When you change the value of the SHARDING attribute to PARTITION, note that:

  • If the table group contains only non-partitioned tables, you can directly change the attribute value.

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

  • If the table group contains only partitioned or subpartitioned tables, make sure that all tables in the table group have the same partitioning type, partition count, and partition value. Otherwise, you cannot modify the attribute.

• When you change the value of the SHARDING attribute to ADAPTIVE, note that:

  • If the table group contains only non-partitioned tables, you can directly change the attribute value.

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

  • If the table group contains both partitioned and subpartitioned tables, you cannot modify the attribute.

  • If the table group contains only partitioned tables, all the tables must have the same partitioning type, partition count, and partition value. Otherwise, you cannot modify the attribute.

  • If all tables in the table group are subpartitioned tables, make sure that all partitioned tables and subpartitioned tables correspond to the same partition type, number of partitions, and partition values.

Modify the SHARDING attribute

The SQL syntax 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'

Some notes about this:

• To execute this statement, you must have the global ALTER privilege.

• tablegroup_name specifies the name of the table group to be modified.

• SHARDING specifies the SHARDING attribute of the table group. You must follow specific rules when you modify the SHARDING attribute of the table group.

Here is an example:

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

   CREATE TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
   

2. Create two tables named tbl1 and tbl2 that have the same partition definition, and add the tables to the table group tblgroup1.

   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 value of the table group tblgroup1 to ADAPTIVE.

   ALTER TABLEGROUP tblgroup1 SHARDING = "ADAPTIVE";
   

4. View the SHARDING attribute of the table group.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
   

   The query 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 have the same partitioning type, partition count, and partition value. Therefore, the SHARDING attribute is successfully modified.

References

• 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