Clear partition weights

After partition weights are set, you can clear the set partition weights based on your business needs.

Limitations

• Only user tenants support manually clearing partition weights. The sys tenant does not support this feature.

• Clearing table-level partition weights deletes the table-level weights set on all partitions. If partition-level weights are set for the table, the system retains the partition-level weights.

Clear partition weights in MySQL-compatible mode

1. Log in to a MySQL tenant of the cluster.

   Here is an example of the connection command. Replace the parameters with those in your actual environment.

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

2. Get the partition weight information 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 table-level weights

     Execute the following statement to clear the partition weights of the table tbl1_h:

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

   • Clear first-level partition weights

     Execute the following statement to clear the partition weight of the first-level partition p0 in the table tbl1_h:

     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 an Oracle tenant of the cluster.

   Here is an example of the connection command. Replace the parameters with those in your actual environment.

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

2. Get the partition weight information of the table.

   obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.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 | 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 table-level weights

     Execute the following statements to clear the partition weights of the table TBL1_H:

     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 first-level partition weights

     Execute the following statements to clear the partition weight of the first-level partition P0 in the table TBL1_H:

     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 partition weights are cleared, the distribution of partitions in the table may be unbalanced. If you want to achieve partition balancing as soon as possible, you can manually trigger a round of partition balancing by calling the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE subprogram. 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 be triggered. For information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.