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

If you need to use the automatic partition load balancing capability of the tenant when you modify partitions of a table, you must enable data balancing for the tenant and set an interval for generating partition load balancing tasks. The automatic partition load balancing feature is controlled by the following two tenant-level parameters:

• enable_rebalance

  You can configure this parameter in the sys tenant to specify whether to implement data balancing between tenants, and in a user tenant to specify whether to implement load balancing within the current tenant. The default value is true. The modification takes effect immediately.

  Valid values of enable_rebalance in the sys tenant include:

  • true: specifies to enable data balancing between tenants.
  • false: specifies to disable data balancing between tenants.

  Valid values of enable_rebalance in a user tenant include:

  • true: specifies to enable data balancing within the current tenant. Before you modify partitions for a table, we recommend that you enable data balancing for the tenant.
  • false: specifies to disable data balancing for the tenant.

• PARTITION_BALANCE_SCHEDULE_INTERVAL

  This parameter specifies an interval for generating partition load balancing tasks. When enable_rebalance is set to true, the system automatically triggers partition load balancing tasks based on the interval specified by partition_balance_schedule_interval. The default value is 2h. The value range is [0s, +∞). The value 0s indicates that partition load balancing is disabled. The modification takes effect immediately.

You can perform the following steps to configure these two parameters:

1. Log on as an administrator to the sys tenant or a user tenant of the cluster.

2. Enable data balancing.

   • Enable data balancing for a specified tenant from the sys tenant

     ALTER SYSTEM SET enable_rebalance = true TENANT = 'tenant_name';
     

     This statement only sets the value of the enable_rebalance parameter to true for the specified user tenant.

     Here is an example:

     ALTER SYSTEM SET enable_rebalance = true TENANT = 'oracle';
     

   • Enable data balancing for all user tenants from the sys tenant

     ALTER SYSTEM SET enable_rebalance = true TENANT = all_user;
     

     or

     ALTER SYSTEM SET enable_rebalance = true TENANT = all;
     

     This statement sets the value of the enable_rebalance parameter to true for all user tenants.

     Note

     In OceanBase Database V4.2.1 and later, the TENANT = all_user and TENANT = all settings express the same semantics. If you want an operation to take effect on all user tenants, we recommend that you use TENANT = all_user. TENANT = all will be deprecated.

   • Enable data balancing for a user tenant from the current tenant

     ALTER SYSTEM SET enable_rebalance = true;
     

3. Set an interval for generating partition load balancing tasks.

   • sys tenant

     ALTER SYSTEM SET partition_balance_schedule_interval='2h' TENANT = 'tenant_name';
     

     Here is an example:

     ALTER SYSTEM SET partition_balance_schedule_interval='2h' TENANT = 'mysql';
     

   • User tenant

     ALTER SYSTEM SET partition_balance_schedule_interval='2h';
     

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, follow the following rules:

• When the SHARDING attribute of the table 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 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       | tbl2       | test          | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   1 rows 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.

• If the SHARDING attribute is set to PARTITION, the table to be moved must 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 means that:

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

• If the SHARDING attribute is set to ADAPTIVE, the table to be moved must 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 means that:

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

FOr example, move the tbl1 table to the table group tblgroup2.

ALTER TABLE tbl1 SET TABLEGROUP tblgroup2;

References

• Table groups

• 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