Manage tables in a table group

After you add a table to a table group, you can manage partitions of the table, remove the table from the table group, or move the table to another table group. For example, you can change the partitioning type, number of partitions, or partition value of the table, add a partition to the table, or drop a partition from the table.

Manage partitions of a table

Considerations

After you modify the partitions of a table, the distribution of data may change, thereby leading to unbalanced resource allocation. Therefore, we recommend that you enable partition balancing for the tenant before you modify a partition. This is to ensure the optimization and balancing of resource allocation.

For more information about data load balancing, see Overview.

Rules for modifying partitions of a table

Before you perform partition management operations on a table in a table group, you can query views for the SHARDING attribute of the table group, tables in the table group, and partition information about tables in the table group. For more information, see Query information about a table group.

If the table group contains only the current table, you can perform partition management operations without limitations. If the table group contains other tables, observe the following rules:

• When the SHARDING attribute of the table group is set to NONE:

  • If the table is a non-partitioned table, you can change it to a partitioned or subpartitioned table.

  • If the table is a partitioned table, you can change it to a subpartitioned table. If the table is a subpartitioned table, you can change it to a partitioned or non-partitioned table. You can also add or drop partitions. In other words, you can change the partitioning type, partition count, and partition value without limitations.

• When the SHARDING attribute of the table group is set to PARTITION:

  • If the table is a non-partitioned table, you cannot change it to a partitioned table.

  • If the table is a partitioned table, you cannot change it to a non-partitioned table.

    • If the table is a partitioned table, you can change it to a subpartitioned table that has the same partitioning type, partition count, and partition value as other tables in the table group. No limitations are imposed on the subpartitioning type, subpartition count, or subpartition value. You can change the number of partitions in the table only by adding or dropping partitions. You are not allowed to take other means to change the partitioning type, partition count, or partition value to a value different from that of other tables in the table group.

    • If the table is a subpartitioned table, you can change it to a partitioned table that has the same partitioning type, partition count, and partition value as other tables in the table group. You can change the number of partitions in the table only by adding or dropping partitions. You are not allowed to take other means to change the partitioning type, partition count, or partition value to a value different from that of other tables in the table group.

    The system does not implement load balancing on a table group for which a partition addition or drop operation has been performed. The system implements load balancing only after the partitioning type, partition count, and partition value of all tables in the table group meet the requirements of the PARTITION attribute value.

• When the SHARDING attribute of the table group is set to ADAPTIVE:

  • If the table is a non-partitioned table, you cannot change it to a partitioned table.

  • If the table is a partitioned table, you cannot change it to a non-partitioned table.

    • If the table group contains only partitioned tables, you cannot change this table to a non-partitioned or subpartitioned table. You can change the number of partitions in the table only by adding or dropping partitions. You are not allowed to take other means to change the partitioning type, partition count, or partition value to a value different from that of other tables in the table group.

    • If the table group contains only subpartitioned tables, you cannot change this table to a partitioned table. You can change the number of partitions in the table only by adding or dropping partitions or subpartitions. You are not allowed to take other means to change the partitioning type, partition count, or partition value to a value different from that of other tables in the table group.

    The system does not implement load balancing on a table group for which a partition/subpartition addition or drop operation has been performed. The system implements load balancing only after the partitioning type, partition count, and partition value of all tables in the table group meet the requirements of the ADAPTIVE attribute value.

Modify partitions of a table

You can refer to the following topics to modify partitions of a table based on the actual situation and modification rules:

• Modify partitioning rules

• Add a partition

• Drop a partition

Remove a table from a table group

After a table is added to a table group, you can execute the following statement to remove it from the table group:

ALTER TABLE table_name SET TABLEGROUP '';

Here is an example:

1. Assume that the table group tblgroup1 contains the tbl1 and tbl2 tables. Execute the following statement to view the tables in the table group:

   SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
   

   A sample query result is as follows:

   +-----------------+------------+---------------+----------+
   | Tablegroup_name | Table_name | Database_name | Sharding |
   +-----------------+------------+---------------+----------+
   | tblgroup1       | tbl1       | test          | ADAPTIVE |
   | tblgroup1       | tbl2       | test          | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   2 rows in set
   

2. Remove the tbl1 table from the table group.

   ALTER TABLE tbl1 SET TABLEGROUP '';
   

3. View tables in the table group.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
   

   A sample query result is as follows:

   +-----------------+------------+---------------+----------+
   | Tablegroup_name | Table_name | Database_name | Sharding |
   +-----------------+------------+---------------+----------+
   | tblgroup1       | tbl2       | test          | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   1 row in set
   

Move a table to another table group

After a table is added to a table group, you can move the table to another table group.

Before you move a table to another table group, check the SHARDING attribute of the target table group and the partition information of existing tables in the table group to verify whether the current table meets the conditions for joining the table group. For more information about how to view the information about a table group, see Query information about a table group.

• If the SHARDING attribute is set to NONE, the table group has no limitations on tables. In this case, you can directly move the table to this table group.

• A table group with the SHARDING attribute set to PARTITION requires that the table to be moved in have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value. Such a table group can contain both partitioned tables and subpartitioned tables.

  Specifically, the same partition definition is described as follows:

  • Tables in the table group have the same partitioning type, such as RANGE partitioning.
  • HASH-partitioned tables have the same number of referenced columns and the same number of partitions.
  • RANGE-partitioned tables have the same number of referenced columns, the same number of partitions, and the same range definition.

• A table group with the SHARDING attribute set to ADAPTIVE requires that the table to be moved in have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value. The tables in the table group must be all partitioned tables or all subpartitioned tables.

  Specifically, the same partition definition is described as follows:

  • Tables in the table group have the same partitioning type such as HASH-RANGE partitioning.
  • HASH-partitioned tables have the same number of referenced columns and the same number of partitions.
  • RANGE-partitioned tables have the same number of referenced columns, the same number of partitions, and the same range definition.
  • The requirements on subpartitions are the same as those on partitions, depending on the partitioning type.

The SQL syntax for moving a table to another table group is as follows:

ALTER TABLE table_name SET TABLEGROUP tablegroup_name;

The following sample code moves the tbl1 table to the tblgroup2 table group.

ALTER TABLE tbl1 SET TABLEGROUP tblgroup2;

References

• Overview

• Create a table group

• Query information about a table group

• Add tables to a table group

• Modify the SHARDING attribute of a table group

• Drop a table group