In OceanBase AI Database, distributed DML hints are used to control whether DML operations are performed in a distributed manner. Note the following two important concepts:
- Distributed DML: Specifically refers to DML operations executed across multiple nodes, which are fundamentally different from non-distributed DML operations that are executed locally or partition-wise on a single node.
- Parallel DML: Refers to the situation where a single DML operation is executed in parallel by multiple threads within a single node.
Furthermore, although such hints can be used to fix execution plans, they are not recommended for general use and are typically enabled only in OUTLINE DATA.
Hint name |
Explanation |
|---|---|
USE_DISTRIBUTED_DML |
Enables distributed DML operations. Its reverse operation is NO_USE_DISTRIBUTED_DML. |
NO_USE_DISTRIBUTED_DML |
Disables distributed DML operations. Its reverse operation is USE_DISTRIBUTED_DML. |
USE_DISTRIBUTED_DML Hint
The USE_DISTRIBUTED_DML hint enables distributed DML operations. After this hint is enabled, DML operations will be executed in a distributed manner. Its counterpart is the NO_USE_DISTRIBUTED_DML hint.
Syntax
/*+ USE_DISTRIBUTED_DML [ ( [ @ qb_name ] ) ] */
Examples
-- Create a table named tp1 with 10 partitions in hash partitioning.
CREATE TABLE tp1(c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 10;
-- Use the USE_DISTRIBUTED_DML hint to update column c2 of table tp1 to 4, where the condition is that the value in column c1 is less than 4.
-- This update operation will be executed in a distributed manner.
EXPLAIN BASIC UPDATE /*+ USE_DISTRIBUTED_DML */ tp1 SET c2 = 4 WHERE c1 < 4;
+--------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------+
| ========================================= |
| |ID|OPERATOR |NAME | |
| ----------------------------------------- |
| |0 |DISTRIBUTED UPDATE | | |
| |1 |└─PX COORDINATOR | | |
| |2 | └─EXCHANGE OUT DISTR |:EX10000| |
| |3 | └─PX PARTITION ITERATOR| | |
| |4 | └─TABLE FULL SCAN |TP1 | |
| ========================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil) |
| table_columns([{TP1: ({TP1: (TP1.__pk_increment, TP1.C1, TP1.C2, TP1.C3)})}]), |
| update([TP1.C2=column_conv(NUMBER,PS:(-1,0),NULL,cast(4, NUMBER(-1, -85)))]) |
| 1 - output([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| 2 - output([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| dop=1 |
| 3 - output([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), filter(nil), rowset=16 |
| force partition granule |
| 4 - output([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), filter([TP1.C1 < cast(4, NUMBER(-1, -85))]), rowset=16 |
| access([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([TP1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------------------------------------------------+
23 rows in set
NO_USE_DISTRIBUTED_DML Hint
The NO_USE_DISTRIBUTED_DML hint prohibits distributed execution for DML operations. Its counterpart is the USE_DISTRIBUTED_DML hint.
Syntax
/*+ NO_USE_DISTRIBUTED_DML [ ( [ @ qb_name ] ) ] */
Examples
-- Create a table named tp1 with 10 partitions in hash partitioning.
CREATE TABLE tp1(c1 INT, c2 INT, c3 INT) PARTITION BY HASH(c1) PARTITIONS 10;
-- Use the NO_USE_DISTRIBUTED_DML hint to restrict update operations from being executed locally on a single node instead of distributed.
-- This update operation will be non-distributed execution.
EXPLAIN BASIC UPDATE /*+ NO_USE_DISTRIBUTED_DML */ tp1 SET c2 = 4 WHERE c1 < 4;
+--------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------+
| ======================================= |
| |ID|OPERATOR |NAME | |
| --------------------------------------- |
| |0 |PX COORDINATOR | | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000| |
| |2 | └─PX PARTITION ITERATOR| | |
| |3 | └─UPDATE | | |
| |4 | └─TABLE FULL SCAN |TP1 | |
| ======================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=16 |
| 1 - output(nil), filter(nil), rowset=16 |
| dop=1 |
| 2 - output(nil), filter(nil), rowset=16 |
| partition wise, force partition granule |
| 3 - output(nil), filter(nil) |
| table_columns([{TP1: ({TP1: (TP1.__pk_increment, TP1.C1, TP1.C2, TP1.C3)})}]), |
| update([TP1.C2=column_conv(NUMBER,PS:(-1,0),NULL,cast(4, NUMBER(-1, -85)))]) |
| 4 - output([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), filter([TP1.C1 < cast(4, NUMBER(-1, -85))]), rowset=16 |
| access([TP1.__pk_increment], [TP1.C1], [TP1.C2], [TP1.C3]), partitions(p[0-9]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([TP1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------------------------------------------------+
23 rows in set