After setting partition weights, you can clear the original weights if business requirements change.
Limitations
Partition weights can be cleared only in user tenants, not in the sys tenant.
If you clear the table-level weight, the table-level weight set for all partitions is removed. If partition-level weights are set for the table, they are retained.
Procedure
Log in to the user tenant of the cluster.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -ARetrieve the partition weight information for the table.
obclient(root@mysql001)[oceanbase]> 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 partition weights for the table.
Clear table-level weights
To clear the partition weights for table
tbl1_h, use the following statement.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('test','tbl1_h');Clear partition-level weights
To clear the partition weight for partition
p0in tabletbl1_h, use the following statement.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('test','tbl1_h','p0');
What to do next
After clearing partition weights, the partition distribution on the table may become unbalanced. If you want to achieve balanced partition distribution as soon as possible, you can call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE subprogram to manually trigger partition balancing. For more information about how to manually trigger partition balancing, see Manually trigger partition balancing.
Alternatively, you can wait for the scheduled partition balancing task to trigger. For more information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.