The SET_BALANCE_WEIGHT statement is used to set partition weights. Partition weights can be set at the table level or partition level, from top to bottom.
Limitations and considerations
Only user tenants support manual partition weight setting; the system tenant does not support it.
Currently, partition weights can only be set for non-partitioned tables and partitions of partitioned tables. Subpartition weights are not supported.
You can only set weights for existing entity partitions. Weights cannot be specified when creating a table. That is, you must create the table first and then set partition weights for it.
Partition weights must be integers within the range [1, +∞). By default, a partition has no weight.
When setting partition weights, it is recommended to use as few weight levels as possible. It is suggested to divide the weights into at most three tiers:
Weight = 100% * Number of partitions
Weight in middle = 50% * Number of partitions
Small weight = 1
For partitioned tables that do not require weight balancing, it is recommended not to set partition weights.
When setting partition weights, child partitions inherit the weight of their parent partition by default. If partition weights are set at multiple levels, the weight at a lower level overrides the weight at a higher level.
After setting partition weights, if you perform an offline DDL operation (such as TRUNCATE TABLE or TRUNCATE PARTITION) that changes the partition entities, the previously set partition weights will become invalid and need to be reset. For offline DDL operations that may cause partition weight invalidation, see Online DDL and Offline DDL Operations.
Syntax
DBMS_BALANCE.SET_BALANCE_WEIGHT(
weight INT,
database_name VARCHAR(65535),
table_name VARCHAR(65535),
partition_name VARCHAR(65535) DEFAULT NULL,
subpartition_name VARCHAR(65535) DEFAULT NULL);
Parameters
Parameter |
Note |
|---|---|
| weight | Partition weight refers to the relative proportion of resources such as CPU, memory, and disk occupied by different partitions. It can only be an integer in the range [1, +∞). |
| database_name | The name of the database to which the table belongs. |
| table_name | Table name |
| partition_name | Partition name |
| subpartition_name | Subpartition name. Syntax only; subpartition weight setting is not currently supported. |
Examples
Assume that you have created a hash partitioned table named
tbl1_hin thetestdatabase.obclient(root@mysql001)[test]> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5;View 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 specific partition information obtained 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 setSet the table-level partition weight of table
tbl1_hto 1.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(1,'test','tbl1_h');After setting the table-level partition weight only for table
tbl1_h, query the partition weight information set for that table.obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;The 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 | +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+ 1 row in setThen set the partition weight of partition
p0in tabletbl1_hto 2.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(2,'test','tbl1_h','p0');Query the partition weight information again.
obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;The 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