首批通过分布式安全可靠测评，为关键业务系统打造
并行 DML
更新时间：2025-05-26 12:16:04
编辑
分享
并行 DML 通过使用并行执行机制来提高对大型数据库的表和索引执行插入、更新、删除等操作，以提高执行效率。对于决策支持系统 (DSS)的数据库，并行 DML 提供了查询和更新功能，是对并行查询功能的补充。对于 OLTP 数据库，并行 DML 操作可以加速批处理作业的运行。
开启和关闭并行 DML
OceanBase 数据库支持在 SQL 语句或会话中显式启用并行 DML。
在 SQL 语句中启用和关闭并行 DML
在 SQL 语句中启用并行 DML，请在语句中插入如下 Hint：
/*+ ENABLE_PARALLEL_DML PARALLEL(3) */
一般情况下，ENABLE_PARALLEL_DML Hint 和 PARALLEL Hint 必须配合使用才能开启并行 DML。不过，当目标表的 Schema 上指定了表级别的并行度时，仅需指定 ENABLE_PARALLEL_DML Hint。
如下示例为同时使用 ENABLE_PARALLEL_DML Hint 和 PARALLEL(n) 参数指定并行度 n，并且 n > 1，并行度 dop=2。
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) NOPARALLEL;
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 11 PARTITION BY HASH(c1) PARTITIONS 3;
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 10 PARTITION BY HASH(c1) PARTITIONS 4;
obclient> EXPLAIN INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(2) */ INTO t1 SELECT * FROM T3;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------- |
| |0 |OPTIMIZER STATS MERGE | |1 |18 | |
| |1 | PX COORDINATOR | |1 |18 | |
| |2 | EXCHANGE OUT DISTR |:EX10001 |1 |18 | |
| |3 | INSERT | |1 |17 | |
| |4 | EXCHANGE IN DISTR | |1 |4 | |
| |5 | EXCHANGE OUT DISTR (HASH)|:EX10000 |1 |4 | |
| |6 | OPTIMIZER STATS GATHER | |1 |4 | |
| |7 | SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |4 | |
| |8 | PX BLOCK ITERATOR | |1 |4 | |
| |9 | TABLE SCAN |t3 |1 |4 | |
| ========================================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil), rowset=256 |
| 2 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil), rowset=256 |
| dop=2 |
| 3 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil) |
| columns([{t1: ({t1: (t1.c1, t1.c2)})}]), partitions(p0), |
| column_values([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]) |
| 4 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil), rowset=256 |
| 5 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil), rowset=256 |
| (#keys=1, [column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)]), dop=2 |
| 6 - output([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)]), filter(nil), rowset=256 |
| 7 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]), filter(nil), rowset=256 |
| access([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2]) |
| 8 - output([t3.c1], [t3.c2]), filter(nil), rowset=256 |
| 9 - output([t3.c1], [t3.c2]), filter(nil), rowset=256 |
| access([t3.c1], [t3.c2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([t3.c1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
34 rows in set
如果要禁用并行 DML，请在语句中插入如下 Hint：
/*+ DISABLE_PARALLEL_DML */
即使会话中启用了并行 DML，您也可以在指定 SQL 语句中使用 DISABLE_PARALLEL_DML Hint 禁用并行 DML。
在会话中启用和关闭并行 DML
默认情况下，即使 SQL语句中使用了 PARALLEL Hint，并行 DML 也是不开启的，所以还需要通过在会话上开启并行 DML。
在会话中启用并行 DML，MySQL 模式下语法为：
SET _FORCE_PARALLEL_DML_DOP = n;
其中 n 大于 1。
在会话中启用并行 DML，Oracle 模式下语法为：
ALTER SESSION ENABLE PARALLEL DML;
Oracle 模式下，如果需要在会话中强制开启并行 DML，请运行以下 SQL 语句：
ALTER SESSION FORCE PARALLEL DML PARALLEL n;
如下示例为，Oracle 模式下在会话中强制开启并行 DML。
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) NOPARALLEL;
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 11 PARTITION BY HASH(c1) PARTITIONS 3;
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 10 PARTITION BY HASH(c1) PARTITIONS 4;
obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 6;
Query OK, 0 rows affected
obclient> EXPLAIN INSERT INTO t2 SELECT * FROM t3;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------------ |
| |0 |OPTIMIZER STATS MERGE | |1 |16 | |
| |1 | PX COORDINATOR | |1 |16 | |
| |2 | EXCHANGE OUT DISTR |:EX10001 |1 |15 | |
| |3 | INSERT | |1 |15 | |
| |4 | EXCHANGE IN DISTR | |1 |2 | |
| |5 | EXCHANGE OUT DISTR (PKEY HASH)|:EX10000 |1 |2 | |
| |6 | OPTIMIZER STATS GATHER | |1 |2 | |
| |7 | SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |2 | |
| |8 | PX BLOCK ITERATOR | |1 |2 | |
| |9 | TABLE SCAN |T3 |1 |2 | |
| ============================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| 2 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| dop=6 |
| 3 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil) |
| columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-2]), |
| column_values([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]) |
| 4 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)], [PARTITION_ID]), filter(nil), |
| rowset=256 |
| 5 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)], [PARTITION_ID]), filter(nil), |
| rowset=256 |
| (#keys=1, [column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)]), dop=6 |
| 6 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| 7 - output([ANONYMOUS_VIEW1.C1], [ANONYMOUS_VIEW1.C2]), filter(nil), rowset=256 |
| access([ANONYMOUS_VIEW1.C1], [ANONYMOUS_VIEW1.C2]) |
| 8 - output([T3.C1], [T3.C2]), filter(nil), rowset=256 |
| 9 - output([T3.C1], [T3.C2]), filter(nil), rowset=256 |
| access([T3.C1], [T3.C2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([T3.C1]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set
需要注意的是，在 SQL 语句中启用并行 DML 时，一般使用由 Hint 指定的并行度来执行查询，其优先级高于会话中强制指定的并行度。Oracle 模式下的示例如下：
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) NOPARALLEL;
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 11 PARTITION BY HASH(c1) PARTITIONS 3;
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT) PARALLEL 10 PARTITION BY HASH(c1) PARTITIONS 4;
obclient> ALTER SESSION FORCE PARALLEL DML PARALLEL 6;
Query OK, 0 rows affected
obclient> EXPLAIN INSERT /*+ PARALLEL(3) */ INTO t2 SELECT * FROM t3;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------------ |
| |0 |OPTIMIZER STATS MERGE | |1 |17 | |
| |1 | PX COORDINATOR | |1 |17 | |
| |2 | EXCHANGE OUT DISTR |:EX10001 |1 |17 | |
| |3 | INSERT | |1 |16 | |
| |4 | EXCHANGE IN DISTR | |1 |3 | |
| |5 | EXCHANGE OUT DISTR (PKEY HASH)|:EX10000 |1 |3 | |
| |6 | OPTIMIZER STATS GATHER | |1 |3 | |
| |7 | SUBPLAN SCAN |ANONYMOUS_VIEW1|1 |3 | |
| |8 | PX BLOCK ITERATOR | |1 |3 | |
| |9 | TABLE SCAN |T3 |1 |3 | |
| ============================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output(nil), filter(nil), rowset=256 |
| 1 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| 2 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| dop=3 |
| 3 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil) |
| columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-2]), |
| column_values([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]) |
| 4 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)], [PARTITION_ID]), filter(nil), |
| rowset=256 |
| 5 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)], [PARTITION_ID]), filter(nil), |
| rowset=256 |
| (#keys=1, [column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)]), dop=3 |
| 6 - output([column_conv(NUMBER,PS:(-1,0),NOT NULL,ANONYMOUS_VIEW1.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,ANONYMOUS_VIEW1.C2)]), filter(nil), rowset=256 |
| 7 - output([ANONYMOUS_VIEW1.C1], [ANONYMOUS_VIEW1.C2]), filter(nil), rowset=256 |
| access([ANONYMOUS_VIEW1.C1], [ANONYMOUS_VIEW1.C2]) |
| 8 - output([T3.C1], [T3.C2]), filter(nil), rowset=256 |
| 9 - output([T3.C1], [T3.C2]), filter(nil), rowset=256 |
| access([T3.C1], [T3.C2]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, |
| range_key([T3.C1]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set
如果要禁用并行 DML，MySQL 模式下的 SQL 语句为：
SET _FORCE_PARALLEL_DML_DOP = 1;
如果要禁用并行 DML，Oracle 模式下的 SQL 语句为：
ALTER SESSION DISABLE PARALLEL DML;
禁用并行 DML 时，即使在 SQL 语句中使用 PARALLEL Hint，也不会执行并行 DML。 在会话中启用并行 DML 时，则并行执行对此会话中的所有 DML 语句都会生效。如果 SQL 语句通过 ENABLE_PARALLEL_DML Hint 启用并行 DML 时，则并行执行仅对指定语句生效。但是，如果没有具有并行属性的表，或者违反了并行操作的限制，即使启用了并行 DML，DML 操作仍然会串行执行。
使用说明
OceanBase 数据库支持如下 SQL 语句的并行执行能力：
INSERT INTO SELECTUPDATEDELETE
如果表上存在下列索引类型，则需要支持并行执行：
- 局部索引
- 单分区全局索引
- 多分区全局索引
OceanBase 数据库不支持以下 SQL 语句使用并行 DML：
REPLACEINSERT INTO ON DUPLICATE KEY UPDATE- 不支持多表 DML 语句
如果表上存在如下属性，也不支持并行 DML：
- 触发器
- 外键
CHECK约束- 唯一索引
