SET_BALANCE_WEIGHT

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

Limitations and considerations

• For V4.2.5, you can set the partition weights manually starting from V4.2.5 BP2.

• Only user tenants support manual setting of partition weights. 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 partition weights for subpartitions is not supported.

• Only existing partitions support weight setting. You cannot specify weights when creating a table. 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 of 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) changes the partition entities, the previously set weights will become invalid and need to be reset. For more information about Offline DDL operations that may cause this, 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[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[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 tbl1_h to 1.

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

4. Query the partition weight information set for tbl1_h after setting the table-level partition weight.

   obclient[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 partition p0 in tbl1_h to 2.

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

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

   obclient[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