SET_BALANCE_WEIGHT

The SET_BALANCE_WEIGHT procedure is used to set the partition weights. You can set the weights for tables and partitions in the order of levels from top to bottom.

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. Subpartitions do not support weight settings.

• Only existing partitions can have weights set. You cannot specify weights when creating a table. You need to create the table first and then set the partition weights.

• Partition weights can only be 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, with a maximum of three tiers:

  • 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 upper-level partitions by default. If weights are set for multiple levels, the lower-level weights will override the upper-level weights.

• 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. For more information about Offline DDL operations that may invalidate partition weights, 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 SYS creates a hash-partitioned table named TBL1_H.

   obclient(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> 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(SYS@oracle001)[SYS]> delimiter //
   

   obclient(SYS@oracle001)t[SYS]> BEGIN 
                 DBMS_BALANCE.SET_BALANCE_WEIGHT(1,'SYS','TBL1_H');
                 END;//
   

   obclient [SYS]> delimiter ;
   

4. After setting the table-level partition weight of TBL1_H, query the partition weight information that has been set.

   obclient(SYS@oracle001)[SYS]> 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 | SYS           | TBL1_H     | NULL           | NULL              | NULL            |      201006 |          NULL |    500002 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   1 row in set
   

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

   obclient(SYS@oracle001)[SYS]> delimiter //
   

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

   obclient(SYS@oracle001)[SYS]> delimiter ;
   

6. Query the partition weight information that has been set again.

   obclient(SYS@oracle001)[SYS]> obclient[SYS]> 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 | SYS           | TBL1_H     | NULL           | NULL              | NULL            |      201006 |          NULL |    500002 |
   |   500002 |       500003 |            NULL |      2 | SYS           | TBL1_H     | P0             | NULL              | NULL            |      201006 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   2 rows in set
   

References

• Intra-tenant balancing

• Set the partition weight

• Clear the partition weight