CLEAR_BALANCE_WEIGHT

CLEAR_BALANCE_WEIGHT is used to clear the partition weight.

Limitations and considerations

• Only user tenants can call the CLEAR_BALANCE_WEIGHT procedure to clear the partition weight. System tenants cannot clear the partition weight.

• Clearing the table-level partition weight deletes all table-level weights set on the partitions. If partition-level weights have been 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 contains a table tbl1_h with a partition weight set.

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

   obclient(root@mysql001)[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 weight for the table tbl1_h.

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

3. Query the partition weight information set for the table after clearing.

   obclient(root@mysql001)[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 weight for the primary partition p0 in the table tbl1_h.

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

5. Query the partition weight information set for the table again after clearing.

   obclient(root@mysql001)[test]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The query result is empty.

References

• Intra-tenant balancing

• Set the partition weight for a table

• Clear the partition weight