Clear partition weights

After setting partition weights, you can clear the original partition weights based on business needs.

Limitations

• You can manually clear the partition weights for user tenants only. You cannot manually clear the partition weights for system tenants.

• Clearing the table-level partition weights deletes the weights set for all partitions. If partition-level weights are set for the table, they will be retained.

Clear partition weights in MySQL-compatible mode

1. Log in to the MySQL-compatible tenant of the cluster.

   The following example shows how to connect to the database. Please adjust the connection details according to your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. Query the partition weights of 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 set
   

3. Clear the partition weights of the table.

   • Clear the table-level partition weights

     Clear the partition weights of the tbl1_h table. The statement is as follows:

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

   • Clear the partition weight

     Clear the partition weight of the partition p0 in the table tbl1_h. The statement is as follows:

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

Clear partition weights in Oracle-compatible mode

1. Log in to the Oracle-compatible tenant of the cluster.

   The following example shows how to connect to the database. Please adjust the connection details according to your actual environment.

   obclient -h10.xx.xx.xx -P2883 -usys@oracle001#obdemo -p***** -A
   

2. Query the partition weights of the table.

   obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   An example of 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
   

3. Clear the partition weights of the table.

   • Clear the table-level partition weights

     Clear the partition weights of the table TBL1_H. The statement is as follows:

     obclient(SYS@oracle001)[SYS]> delimiter //
     

     obclient(SYS@oracle001)[SYS]> BEGIN
           DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('SYS','TBL1_H');
           END;//
     

     obclient(SYS@oracle001)[SYS]> delimiter ;
     

   • Clear the partition weight

     Clear the partition weight of the partition P0 in the table TBL1_H. The statement is as follows:

     obclient(SYS@oracle001)[SYS]> delimiter //
     

     obclient(SYS@oracle001)[SYS]> BEGIN
         DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('SYS','TBL1_H','P0');
         END;//
     

     obclient(SYS@oracle001)[SYS]> delimiter ;
     

What to do next

After clearing the partition weights, the partition distribution on the table may become unbalanced. If you want to achieve partition balancing as soon as possible, you can call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure to manually trigger a partition balancing task. For more information on manually triggering a partition balancing task, see Manually trigger a partition balancing task.

Alternatively, you can wait for the scheduled partition balancing task to be triggered. For more information on scheduled partition balancing tasks, see Configure a scheduled partition balancing task.