SET_BALANCE_WEIGHT

SET_BALANCE_WEIGHT is used to set the partition weights. You can set the weights of a table or its partitions.

Limitations and considerations

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

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

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

• Only existing partitions can have weights set. You cannot specify weights when creating a table. You must first create the table 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 no more than 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.

• By default, lower-level partitions inherit the weights of higher-level partitions. If weights are set for 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 a list of Offline DDL operations that may cause this, see Online DDL and Offline DDL operations (Oracle mode).

Syntax

DBMS_BALANCE.SET_BALANCE_WEIGHT(
    weight              IN BINARY_INTEGER,
    schema_name         IN VARCHAR2,
    table_name          IN VARCHAR2,
    partition_name      IN VARCHAR2 DEFAULT NULL,
    subpartition_name   IN VARCHAR2 DEFAULT NULL);

Parameters

Examples

1. Assume that user USR2 creates a hash-partitioned table named TBL1_H.

   obclient[USR2]> CREATE TABLE TBL1_H(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5; 
   

2. View the partition information of the table. The statement is as follows.

   obclient[USR2]> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TBL1_H';
   

   The specific partition information is as follows:

   +------------+----------------+
   | TABLE_NAME | PARTITION_NAME |
   +------------+----------------+
   | TBL1_H     | P4             |
   | TBL1_H     | P3             |
   | TBL1_H     | P2             |
   | TBL1_H     | P1             |
   | TBL1_H     | P0             |
   +------------+----------------+
   5 rows in set
   

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

   obclient [USR2]> delimiter //
   

   obclient[USR2]> BEGIN 
                 DBMS_BALANCE.SET_BALANCE_WEIGHT(1,'USR2','TBL1_H');
                 END;//
   

   obclient [USR2]> delimiter ;
   

4. Query the partition weights set for TBL1_H.

   obclient[USR2]> SELECT * FROM SYS.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 | USR2          | TBL1_H     | NULL           | NULL              | NULL            |      201006 |          NULL |    500002 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   1 row in set
   

5. Set the partition weight of the partition P0 in TBL1_H to 2.

   obclient [USR2]> delimiter //
   

   obclient[USR2]> BEGIN 
                 DBMS_BALANCE.SET_BALANCE_WEIGHT(2,'USR2','TBL1_H','P0');
                 END;//
   

   obclient [USR2]> delimiter ;
   

6. Query the partition weights set for TBL1_H again.

   obclient[USR2]> obclient[USR2]> SELECT * FROM SYS.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The query 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 | USR2          | TBL1_H     | NULL           | NULL              | NULL            |      201006 |          NULL |    500002 |
   |   500002 |       500003 |            NULL |      2 | USR2          | TBL1_H     | P0             | NULL              | NULL            |      201006 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   2 rows in set
   

References

• Intra-tenant balancing