SET_BALANCE_WEIGHT

SET_BALANCE_WEIGHT is used to set the partition weights. The weights can be set at the table level and partition level.

Limitations and considerations

• Only user tenants support manual partition weight setting. System tenants do not support setting partition weights.

• Currently, partition weights can only be set for non-partitioned tables and partitions of partitioned tables. Setting weights for subpartitions is not supported.

• Weights can only be set for existing partitions. Specifying weights during table creation is not supported. You need to create the table first and then set the partition weights.

• Partition weights are integers in the range [1, +∞). By default, partitions do not have weights.

• When setting partition weights, it is recommended to use as few weight levels as possible, with a maximum of three levels:

  • High weight = 100% * number of partitions

  • Medium weight = 50% * number of partitions

  • Low weight = 1

• For partitioned tables that do not need to participate in weight balancing, it is recommended not to set partition weights.

• When setting partition weights, subpartitions inherit the weights from their parent partitions by default. If weights are set at multiple levels, the lower-level weights will override the higher-level ones.

• After setting partition weights, if an offline DDL operation (such as Truncate Table or Truncate Partition) is performed, the original partition weights will become invalid and need to be reset. For a list of offline DDL operations that may cause partition weights to become invalid, see Online DDL and Offline DDL operations (MySQL mode).

Syntax

DBMS_BALANCE.SET_BALANCE_WEIGHT(
    weight              INT,
    database_name       VARCHAR(65535),
    table_name          VARCHAR(65535),
    partition_name      VARCHAR(65535) DEFAULT NULL,
    subpartition_name   VARCHAR(65535) DEFAULT NULL);

Parameters

Examples

1. Assume that a user has created a hash-partitioned table named tbl1_h in the test database.

   obclient(root@mysql001)[test]> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5; 
   

2. View the partition information of the table.

   obclient(root@mysql001)[test]> SELECT TABLE_NAME, PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME ='tbl1_h';
   

   The specific partition information is as follows:

   +------------+----------------+
   | TABLE_NAME | PARTITION_NAME |
   +------------+----------------+
   | tbl1_h     | p0             |
   | tbl1_h     | p3             |
   | tbl1_h     | p2             |
   | tbl1_h     | p4             |
   | tbl1_h     | p1             |
   +------------+----------------+
   5 rows in set
   

3. Set the table-level partition weight of the tbl1_h table to 1.

   obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(1,'test','tbl1_h');
   

4. Query the partition weight information set for the tbl1_h table.

   obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The result is as follows:

   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   | TABLE_ID | PARTITION_ID | SUBPARTITION_ID | WEIGHT | DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLEGROUP_NAME | DATABASE_ID | TABLEGROUP_ID | OBJECT_ID |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   |   500002 |         NULL |            NULL |      1 | test          | tbl1_h     | NULL           | NULL              | NULL            |      500001 |          NULL |    500002 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   1 row in set
   

5. Set the partition weight of the p0 primary partition in the tbl1_h table to 2.

   obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(2,'test','tbl1_h','p0');
   

6. Query the partition weight information set for the tbl1_h table again.

   obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The result is as follows:

   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   | TABLE_ID | PARTITION_ID | SUBPARTITION_ID | WEIGHT | DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLEGROUP_NAME | DATABASE_ID | TABLEGROUP_ID | OBJECT_ID |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   |   500002 |         NULL |            NULL |      1 | test          | tbl1_h     | NULL           | NULL              | NULL            |      500001 |          NULL |    500002 |
   |   500002 |       500003 |            NULL |      2 | test          | tbl1_h     | p0             | NULL              | NULL            |      500001 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   2 rows in set
   

References

• Intra-tenant balancing

• Set the partition weight

• Clear the partition weight