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 Data load balancing overview.
Notice
- The scheduled partition balancing task
SCHEDULED_TRIGGER_PARTITION_BALANCEis enabled by default (enabled = 1). - The tenant-level parameter
partition_balance_schedule_intervaldefaults to 0. Partition balancing is triggered solely by scheduled tasks or manual user initiation. For information about scheduled tasks and manual triggering, see **Configure a scheduled partition balancing task** and **Manually trigger partition balancing**. - If you set
partition_balance_schedule_interval, the system verifies thatSCHEDULED_TRIGGER_PARTITION_BALANCEis disabled; otherwise, an errorOB_OP_NOT_ALLOWis returned.
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 group, and the partition information of the tables within the 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 without restrictions. If the table group contains other tables besides the current one, you must follow these modification rules:
When the
SHARDINGattribute of the table's table group isNONE:If the table is a non-partitioned table, you can modify it to a partitioned table or a subpartitioned table.
If the table is a partitioned table, you can modify it to a subpartitioned table, or modify a subpartitioned table to a partitioned table. You can also add or delete partitions, meaning there are no restrictions on partition type, number of partitions, or partition values.
When the
SHARDINGattribute of the table's table group isPARTITION:If the table is a non-partitioned table, you cannot modify it to a partitioned table.
If the table is a partitioned table, you cannot modify it to a non-partitioned table.
If the table is a partitioned table, you are allowed to change it to a subpartitioned table. The partition type, number of partitions, and partition values of the primary partitions of the subpartitioned table must be consistent with those of other tables in the table group. There are no restrictions on the subpartition type, number of subpartitions, or subpartition values of the subpartitioned table. Except for changing the number of partitions by adding or deleting partitions, you are not allowed to change 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, you are allowed to change it to a partitioned table. The partition type, number of partitions, and partition values of the primary partitions of the subpartitioned table must be consistent with those of other tables in the table group. Except for changing the number of partitions by adding or deleting partitions, you are not allowed to change 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 does 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
PARTITIONattribute requirements.
When the
SHARDINGattribute of the table's table group isADAPTIVE:If the table is a non-partitioned table, you cannot modify it to a partitioned table.
If the table is a partitioned table, you cannot modify it to a non-partitioned table.
If the table group consists entirely of partitioned tables, you cannot modify this table to a non-partitioned table or a subpartitioned table. Also, except for adding or deleting partitions to modify the number of partitions, you cannot use other methods to change the partition type, number of partitions, or partition values of this table to values different from those of other tables in the table group.
If the table group consists entirely of subpartitioned tables, you cannot modify this table to a partitioned table. Also, except for adding or deleting partitions or subpartitions to modify the number of partitions, you cannot use other methods to change the partition type, number of partitions, or partition values of this table to values different from those of other tables in the table group.
For a table group that has undergone operations such as adding or dropping a partition or subpartition, load balancing is not 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 requirements of the
ADAPTIVEattribute.
When the
SHARDINGattribute of a table group isSUBPARTITION: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 the 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:
ScenarioPrimary Table SchemaHandlingCREATE 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.
Modification rules for tables within a SUBPARTITION table group
- A partitioned table cannot be changed to a non-partitioned table.
- Except for adding or dropping a partition or subpartition which can change the number of partitions, 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 changed to a partitioned table.
- For a table group that has undergone operations such as adding or dropping partitions, load balancing is not performed. Load balancing will only occur when the partition type and other characteristics 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 using the following SQL statement.
ALTER TABLE table_name SET TABLEGROUP '';
The following example illustrates this process.
Suppose there is a table group named
tblgroup1, which contains two tables:tbl1andtbl2.SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';Query result:
+-----------------+------------+---------------+----------+ | Tablegroup_name | Table_name | Database_name | Sharding | +-----------------+------------+---------------+----------+ | tblgroup1 | tbl1 | test | ADAPTIVE | | tblgroup1 | tbl2 | test | ADAPTIVE | +-----------------+------------+---------------+----------+ 2 rows in setMove the table
tbl1out of the table group.ALTER TABLE tbl1 SET TABLEGROUP '';View the tables in the table group again.
SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';Query result:
+-----------------+------------+---------------+----------+ | Tablegroup_name | Table_name | Database_name | Sharding | +-----------------+------------+---------------+----------+ | tblgroup1 | tbl2 | test | ADAPTIVE | +-----------------+------------+---------------+----------+ 1 rows 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.
For a table group with
SHARDINGattribute set toNONE, there are no restrictions on the table to be migrated; it can be directly migrated to this table group.For a table group with
SHARDINGattribute set toPARTITION, the primary partition of 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 mean:
- The same partition type, for example, both are RANGE partitions.
- For HASH partitions, the same number of referenced columns and the same number of partitions are required.
- For RANGE partitions, the same number of referenced columns, the same number of partitions, and the same RANGE split points are required.
For a table group with
SHARDINGattribute set toADAPTIVE, the primary and subpartitions of 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 mean:
- The same partition type (for example, both are HASH + RANGE partitions).
- For HASH partitions, the same number of referenced columns and the same number of partitions are required.
- For RANGE partitions, the same number of referenced columns, the same number of partitions, and the same RANGE split points are required.
- For subpartitions, the requirements are the same as those for 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;