The CLEAR_BALANCE_WEIGHT statement is used to clear partition weights.
Limitations and considerations
Only user tenants can clear partition weights by calling the
CLEAR_BALANCE_WEIGHTsubprogram. The system tenant does not support clearing partition weights.Clearing the table-level partition weight removes the table-level weight set for all partitions. If the table has a partition-level weight set, the system will retain that weight.
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
Parameter |
Note |
|---|---|
| database_name | The name of the database to which the table belongs. |
| table_name | Table name |
| partition_name | Partition name |
| subpartition_name | Subpartition name. Syntax only; subpartition weight setting is not currently supported. |
Examples
Assume that in the user's
testdatabase, there is a table namedtbl1_hwith partition weights set.Query its partition weight information with the following statement:
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 setClear the table-level partition weight of table
tbl1_h.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('test','tbl1_h');After clearing, query the partition weight information that was set for the table.
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 setClear the partition weight of partition
p0in tabletbl1_h.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('test','tbl1_h','p0');After clearing, query the partition weight information set for the table again.
obclient(root@mysql001)[test]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;The query result is empty.