Set partition weights

You can set partition weights for tables based on your business needs. From top to bottom, you can set weights at the table level and the partition level.

Limitations and considerations

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

• Currently, you can set partition weights only for non-partitioned tables and the partitions of partitioned tables. Setting partition weights for subpartitions is not supported.

• You can set weights only for existing entity partitions. You cannot specify weights when creating a table. That is, you must create a table before setting partition weights for the table.

• Partition weights support only integers, ranging from [1, +∞). By default, partitions do not have weights.

• When you set partition weights, we recommend that you use as few weight levels as possible, with no more than three levels:

  • High weight = 100% × the number of partitions

  • Medium weight = 50% × the number of partitions

  • Low weight = 1

• For partitioned tables that do not need to participate in weight balancing, we recommend that you do not set partition weights.

• When you set partition weights, lower-level partitions inherit the weights of the upper level by default. If you set partition weights for multiple levels at the same time, the lower-level weights overwrite the upper-level weights.

• After partition weights are set, if you perform an offline DDL operation (such as TRUNCATE TABLE or TRUNCATE PARTITION) that changes partition entities, the set partition weights become invalid and you need to set the partition weights again. For offline DDL operations that may invalidate partition weights, see Online DDL and offline DDL operations (MySQL-compatible mode) and Online and offline DDL operations (Oracle-compatible mode).

Set 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. (Optional) Create a table for which you want to set partition weights.

   For example, create a HASH-partitioned table named tbl1_h.

   obclient(root@mysql001)[test]> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5;
   

3. (Optional) Get the partition information of the table.

   obclient(root@mysql001)[test]> SELECT TABLE_NAME, PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME ='tbl1_h';
   

   The query result is as follows:

   +------------+----------------+
   | TABLE_NAME | PARTITION_NAME |
   +------------+----------------+
   | tbl1_h     | p0             |
   | tbl1_h     | p3             |
   | tbl1_h     | p2             |
   | tbl1_h     | p4             |
   | tbl1_h     | p1             |
   +------------+----------------+
   5 rows in set
   

4. Based on your business needs, set partition weights at a suitable level.

   Partition weights support only integers, ranging from [1, +∞). By default, partitions do not have weights.

   • Set table-level weights

     You can set table-level partition weights for partitioned or non-partitioned tables. For example, execute the following statement to set the partition weight of the partitioned table tbl1_h to 1:

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

   • Set first-level partition weights

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

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

5. After the weights are set, view the partition weight information of 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 |         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
   

Set 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. (Optional) Create a table for which you want to set partition weights.

   For example, create a HASH-partitioned table named TBL1_H.

   obclient(SYS@oracle001)[SYS]> CREATE TABLE TBL1_H(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5;
   

3. (Optional) Get the partition information of the table.

   obclient(SYS@oracle001)[SYS]> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TBL1_H';
   

   The query result is as follows:

   +------------+----------------+
   | TABLE_NAME | PARTITION_NAME |
   +------------+----------------+
   | TBL1_H     | P4             |
   | TBL1_H     | P3             |
   | TBL1_H     | P2             |
   | TBL1_H     | P1             |
   | TBL1_H     | P0             |
   +------------+----------------+
   5 rows in set
   

4. Based on your business needs, set partition weights at a suitable level.

   Partition weights support only integers, ranging from [1, +∞). By default, partitions do not have weights.

   • Set table-level weights

     You can set table-level partition weights for partitioned or non-partitioned tables. For example, execute the following statements to set the partition weight of the partitioned table TBL1_H to 1:

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

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

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

   • Set first-level partition weights

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

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

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

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

5. After the weights are set, view 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
   

What to do next

After partition weights are set, if you want to perform weight-based partition balancing, 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.

References

For an application case of partition weights, see Application of partition weight balancing in Intra-tenant balancing.