CLEAR_BALANCE_WEIGHT

The CLEAR_BALANCE_WEIGHT procedure is used to clear partition weights.

Limitations and considerations

• Starting from V4.2.5 BP2, the CLEAR_BALANCE_WEIGHT procedure is supported for V4.2.5.

• Only user tenants can clear partition weights by calling the CLEAR_BALANCE_WEIGHT procedure. System tenants cannot clear partition weights.

• Clearing table-level partition weights removes all table-level weights set on the partitions. If partition-level weights were set for the table, they will be retained.

Syntax

DBMS_BALANCE.CLEAR_BALANCE_WEIGHT(
    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 the test database of the user has a table named tbl1_h with partition weights set.

   Query the partition weight information of the table. The SQL statement is as follows:

   obclient[test]> SELECT * FROM oceanbase.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 | 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
   

2. Clear the table-level partition weights of the tbl1_h table.

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

3. Query the partition weights set for the table after clearing.

   obclient[test]> SELECT * FROM oceanbase.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 |       500003 |            NULL |      2 | test          | tbl1_h     | p0             | NULL              | NULL            |      500001 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   1 row in set
   

4. Clear the partition weights of the p0 partition in the tbl1_h table.

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

5. Query the partition weights set for the table again after clearing.

   obclient[test]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The query result is empty.

References

• Intra-tenant balancing