SET_BALANCE_WEIGHT

The SET_BALANCE_WEIGHT procedure is used to set the partition weights. You can set the partition weights for tables and partitions.

Limitations and considerations

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

• 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 physical partitions can have weights set. 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 tiers as possible, ideally up to three:

  • 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, lower-level partitions inherit the weights from higher-level partitions. 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 partition weights will become invalid and need to be reset. See Online DDL and Offline DDL operations (MySQL-compatible mode) for a list of Offline DDL operations that may invalidate partition weights.

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 table tbl1_h to 1.

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

4. After setting the table-level partition weight of the table tbl1_h, query the partition weight information set for the 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 primary partition p0 in the table tbl1_h 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 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 for a table

• Clear the partition weight