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.

• Take note of the following points when you change the value of the SHARDING attribute to PARTITION:

  • 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.

• Take note of the following points when you change the value of the SHARDING attribute to ADAPTIVE:

  • 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 the table group contains only subpartitioned tables, all the tables must have the same partitioning type, partition count, and partition value. Otherwise, you cannot modify the attribute.

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'

The parameters in the syntax are described as follows:

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

• SHARDING: 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 partitioned tables named tbl1 and tbl2 that have the same partition definition, and add the tables to the tblgroup1 table group.

   CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES('01'),
   PARTITION p1 VALUES('02')
   );
   

   CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES('01'),
   PARTITION p1 VALUES('02')
   );
   

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

   ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';
   

4. View the SHARDING attribute of the table group.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'TBLGROUP1';
   

   The return result is as follows:

   +-----------------+------------+---------------+----------+
   | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME | SHARDING |
   +-----------------+------------+---------------+----------+
   | TBLGROUP1       | TBL1       | SYS           | ADAPTIVE |
   | TBLGROUP1       | TBL2       | SYS           | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   2 rows in set
   

   The tbl1 and tbl2 tables in the table group are both partitioned tables and have the same partitioning type, partition count, and partition value. Therefore, the SHARDING attribute is successfully modified.

References

• Overview

• 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