Manage tables within a table group

After a table is added to a table group, you can perform operations on the table such as partition management (for example, modifying the partition type, number of partitions, partition values, adding partitions, and dropping partitions), removing the table from the table group, and migrating the table to another table group.

Manage table partitions

Considerations for modifying table partitions

After modifying table partitions, the data distribution may change, which could lead to uneven resource allocation. Therefore, it is recommended to enable partition balancing for the tenant when modifying table partitions to ensure optimized and balanced resource allocation.

For information about data load balancing, see Overview of data load balancing.

Rules for modifying table partitions

Before performing partition management operations on tables within a table group, you can first view the SHARDING attribute of the current table's table group, the tables present in the table group, and the partition information of the tables within the table group through views. For detailed operations, see View table group information.

Based on the query results, if the table group contains only the current table, you can perform partition management operations normally without restrictions. If the table group contains other tables besides the current one, you must follow these modification rules:

• When the SHARDING attribute of the table's table group is NONE:

  • If the table is a non-partitioned table, it is allowed to be changed to a partitioned table or a subpartitioned table.

  • If the table is a partitioned table, it is allowed to be changed from a partitioned table to a subpartitioned table, or from a subpartitioned table to a partitioned table. Additionally, adding or deleting partitions is allowed, meaning there are no restrictions on the partition type, number of partitions, or partition values.

• When the SHARDING attribute of the table's table group is PARTITION:

  • If the table is a non-partitioned table, it is not allowed to be modified into a partitioned table.

  • If the table is a partitioned table, it is not allowed to be modified into a non-partitioned table.

    • If the table is a partitioned table, it is allowed to be modified into a subpartitioned table. The partition type, number of partitions, and partition values of the subpartitions corresponding to the original partitions must be consistent with those of other tables in the table group. There are no restrictions on the definition of the subpartition type, number of subpartitions, or subpartition values. Apart from modifying the number of partitions by adding or deleting partitions, it is not allowed to modify the partition type, number of partitions, or partition values of a partition to values different from those of other tables in the table group by other means.

    • If the table is a subpartitioned table, it is allowed to be modified into a partitioned table. The partition type, number of partitions, and partition values of the resulting partition must be consistent with those of other tables in the table group. Apart from modifying the number of partitions by adding or deleting partitions, it is not allowed to modify the partition type, number of partitions, or partition values of a partition to values different from those of other tables in the table group by other means.

    For a table group that has undergone adding or deleting partitions, the system will not perform load balancing. Load balancing will only occur when the partition type, number of partitions, and partition values of all tables in the table group meet the requirements of the PARTITION attribute.

• When the SHARDING attribute of the table's table group is ADAPTIVE:

  • If the table is a non-partitioned table, it is not allowed to be modified into a partitioned table.

  • If the table is a partitioned table, it cannot be modified to a non-partitioned table.

    • If all tables in the table group are partitioned tables, the table cannot be modified to a non-partitioned or subpartitioned table. Furthermore, except for modifying the number of partitions by adding or dropping partitions, the partition type, number of partitions, and partition values of the table cannot be changed to values different from those of other tables in the table group.

    • If all tables in the table group are subpartitioned tables, the table cannot be modified to a partitioned table. Furthermore, except for modifying the number of partitions by adding or dropping partitions or subpartitions, the partition type, number of partitions, and partition values of the table cannot be changed to values different from those of other tables in the table group.

    For a table group that has undergone additions or deletions of partitions or subpartitions, load balancing will not be performed. Load balancing will only occur when the partition type, number of partitions, and partition values of all tables in the table group meet the ADAPTIVE attribute requirements.

• When the SHARDING attribute of a table group is SUBPARTITION:

  • Creating a table:

    • If other tables exist in the table group: Log streams are allocated based on the distribution method of the first table in the table group.
    • If no tables exist in the table group: Log streams are allocated independently. At the same time, subpartitions with the same subpartitioning key value under different partitions are guaranteed to be allocated to the same log stream. Subsequent background balancing threads will perform distribution alignment.

  • Adding a partition to an existing table: Adding a partition may break constraints within the table group. The partition is allocated independently, and subsequent balancing attempts will attempt alignment.

  • Creating a global index: Special handling is performed when the partitioning method of the global index meets the implicit binding conditions required by the primary table. Otherwise, it is allocated as a regular table. The handling for several different scenarios is as follows:

    Scenario	Primary Table Schema	Handling
    CREATE INDEX ON EXISTING TABLE	Visible	Allocate log streams in alignment with the primary table
    CREATE TABLE ... WITH INDEX	Invisible	Allocate independently; subsequent balance will align
    Partitioning methods do not match	Visible	Allocate independently; no alignment is performed

Modify table partitions

Modify table partitions based on the actual situation of the current table and the modification rules. Refer to the following content:

• Modify partitioning method

• Add a partition

• Drop a partition

• Set partition weights

• Clear partition weights

Modification rules for tables within a SUBPARTITION table group

• A partitioned table cannot be converted to a non-partitioned table.
• Except for modifying the number of partitions by adding or dropping partitions or subpartitions, the partition type, number of partitions, and partition values of a partition or subpartition cannot be changed to values different from those of other tables in the table group.
• A subpartitioned table cannot be converted to a partitioned table.
• For a table group that has undergone additions or deletions of partitions or subpartitions, load balancing will not be performed. Load balancing will only occur when the partition type and other attributes of all tables in the table group meet the requirements of the table group attributes.

Move a table out of a table group

After a table is added to a table group, it can be removed from the table group. The SQL statement is as follows.

ALTER TABLE table_name SET TABLEGROUP '';

The following example illustrates this.

1. Suppose there is a table group named TBLGROUP1, which contains two tables: TBL1 and TBL2.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'TBLGROUP1';
   

   Query result:

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

2. Move the tbl1 table out of the table group.

   ALTER TABLE tbl1 SET TABLEGROUP '';
   

3. Check the tables in the table group again.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'TBLGROUP1';
   

   Query result:

   +-----------------+------------+---------------+----------+
   | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME | SHARDING |
   +-----------------+------------+---------------+----------+
   | TBLGROUP1       | TBL2       | SYS           | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   1 row in set
   

Migrate a table to another table group

After a table is added to a table group, you can migrate it to another eligible table group.

When migrating a table to another table group, you need to check the attributes of the target table group and the partition information of the tables within the table group to confirm whether the current table meets the requirements for joining the table group. For detailed operations on viewing table group information, see View table group information.

Based on the SHARDING attribute of the table group:

• For a table group with a SHARDING attribute of NONE, there are no restrictions on the table to be migrated; it can be directly migrated to this table group.

• For a table group with a SHARDING attribute of PARTITION, the primary partition corresponding to the table to be migrated must have the same partition definition as the primary partitions of all tables in the table group, including partition type, number of partitions, and partition values. The table group can contain both partitioned tables and subpartitioned tables.

  Identical partition definitions specifically refer to:

  • The same partition type, for example, both are RANGE partitions.
  • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
  • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.

• For a table group with a SHARDING attribute of ADAPTIVE, the primary and subpartitions corresponding to the table to be migrated must have the same partition definition as the primary and subpartitions of all tables in the table group, including partition type, number of partitions, and partition values. The table group must consist entirely of either partitioned tables or subpartitioned tables.

  Identical partition definitions specifically refer to:

  • The same partition type (for example, both are HASH + RANGE partitions).
  • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
  • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.
  • For subpartitions, the requirements are the same as those for primary partitions based on their partition type.

The SQL statement for migrating a table to another table group is as follows.

ALTER TABLE table_name SET TABLEGROUP tablegroup_name;

The following example shows how to migrate table tbl1 to table group tblgroup2.

ALTER TABLE tbl1 SET TABLEGROUP tblgroup2;

References

• About table groups

• Create a table group

• View table group information

• Add a table to a table group

• Modify the SHARDING attribute of a table group

• Delete a table group