SET_BALANCE_WEIGHT

The SET_BALANCE_WEIGHT procedure is used to set the partition weights. It supports setting the partition weights at the table level and the partition level, from top to bottom.

Limitations and considerations

• Only user tenants support manual setting of partition weights, while 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.

• Partition weights can only be set for existing partitions. Specifying weights during table creation is not supported. Therefore, 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, 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 executed, the partition weights will become invalid and need to be reset. For more information about Offline DDL operations that can invalidate partition weights, see Online DDL and Offline DDL operations (Oracle-compatible 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 the SYS user 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 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