Set partition weights

You can set partition weights for a table as needed. By level from top to bottom, you can set partition weights at the table level and at the partition level.

Limitations and considerations

• Only user tenants support manual partition weight settings, while system tenants do not.

• Currently, partition weights can only be set for non-partitioned tables and partitions of partitioned tables. Subpartitions do not support this feature.

• Partition weights can only be set for existing partitions. You cannot specify weights when creating a table. You must create the table first and then set the partition weights.

• Partition weights are integers in the range [1, +∞). By default, partitions do not have weights.

• When setting partition weights, it is recommended to use as few weight tiers as possible, with a maximum of three tiers:

  • High weight = 100% * Number of partitions

  • Medium weight = 50% * Number of partitions

  • Low weight = 1

• For partitioned tables that do not need to participate in weight balancing, it is recommended not to set partition weights.

• When setting partition weights, lower-level partitions inherit the weights from upper-level partitions by default. If weights are set at multiple levels, the lower-level weights will override the upper-level ones.

• After setting partition weights, if you perform an offline DDL operation (such as Truncate Table or Truncate Partition) that causes the partition entity to change, the partition weights will become invalid and you need to reset them. For a list of offline DDL operations that can cause this, see Online and offline DDL operations (MySQL-compatible mode) and Online and offline DDL operations (Oracle-compatible mode).

Set the partition weight 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 replace the actual environment parameters as needed.

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

2. (Optional) Create a table to set the partition weight.

   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) Query 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. Set the partition weight based on your business requirements.

   Partition weights are integers in the range [1, +∞). By default, partitions do not have weights.

   • Set the table-level partition weight.

     You can set the table-level partition weight for a partitioned table or a non-partitioned table. For example, set the partition weight of the partitioned table tbl1_h to 1. The statement is as follows:

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

   • Set the first-level partition weight.

     Set the partition weight of the first-level partition p0 in the table tbl1_h to 2. The statement is as follows:

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

5. After completion, 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 the partition weight 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. (Optional) Create a table to set the 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) Query 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. Set the partition weights at the appropriate level based on your business requirements.

   The partition weights are integers in the range [1, +∞). By default, partitions do not have weights.

   • Set the table-level partition weights

     You can set the table-level partition weights for a partitioned table or a non-partitioned table. For example, set the partition weights of the partitioned table TBL1_H to 1. The statement is as follows:

     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 the first-level partition weight

     Set the partition weight of the first-level partition P0 in the table TBL1_H to 2. The statement is as follows:

     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 completion, 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 setting the partition weights, if you want to perform partition weight balancing, you can call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE procedure to manually trigger a partition balancing task. For more information, see Manually trigger a partition balancing task.

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

References

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