Parallel DML improves the efficiency of inserting, updating, and deleting data in large database tables and indexes by using parallel execution mechanisms. For decision support systems (DSS), parallel DML enhances query and update capabilities, complementing parallel query features. In OLTP databases, parallel DML accelerates batch processing tasks.
Enable and disable parallel DML
OceanBase Database allows you to explicitly enable parallel DML in an SQL statement or a session.
Enable and disable parallel DML in an SQL statement
You can enable parallel DML in an SQL statement by adding the following hint to the statement:
/*+ ENABLE_PARALLEL_DML PARALLEL(3) */
In most cases, you must use the ENABLE_PARALLEL_DML hint in combination with the PARALLEL hint to enable parallel DML. However, if the target table has a global parallel degree specified at the table level, you can use only the ENABLE_PARALLEL_DML hint.
The following example shows how to enable parallel DML by using the ENABLE_PARALLEL_DML hint and the PARALLEL(n) hint, and set the parallelism to n and n > 1, respectively.
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
To disable parallel DML in an SQL statement, add the following hint to the statement:
/*+ DISABLE_PARALLEL_DML */
You can disable parallel DML in a specific SQL statement even if it is enabled in the session.
Enable and disable parallel DML in a session
By default, parallel DML is not enabled even if the PARALLEL hint is used in an SQL statement in a session. You still need to enable parallel DML for the session.
The syntax to enable parallel DML in a session in MySQL mode is as follows:
SET _FORCE_PARALLEL_DML_DOP = n;
where n is greater than 1.
The syntax to enable parallel DML in a session in Oracle mode is as follows:
ALTER SESSION ENABLE PARALLEL DML;
To forcibly enable parallel DML in a session in Oracle mode, execute the following statement:
ALTER SESSION FORCE PARALLEL DML PARALLEL n;
The following example shows how to forcibly enable parallel DML in a session in Oracle mode.
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
Note that when you enable parallel DML in an SQL statement, the parallelism specified in the hint takes precedence over the parallelism forcibly specified for the session. An example in Oracle mode is as follows:
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
To disable parallel DML, you can execute the following statement in MySQL mode:
SET _FORCE_PARALLEL_DML_DOP = 1;
To disable parallel DML, you can execute the following statement in Oracle mode:
ALTER SESSION DISABLE PARALLEL DML;
When you disable parallel DML, even if the PARALLEL hint is used in an SQL statement, parallel DML will not be performed. If you enable parallel DML for a session, parallel execution will be applied to all DML statements in the session. If you use the ENABLE_PARALLEL_DML hint to enable parallel DML for an SQL statement, parallel execution will only be applied to that statement. However, if there is no table with parallelism or if parallel operations are not allowed, DML operations will still be performed in series even if parallel DML is enabled.
Considerations
OceanBase Database supports the parallel execution of the following SQL statements:
INSERT INTO SELECTUPDATEDELETE
If any of the following index types exist on the table, parallel execution is supported:
- Local indexes
- Global indexes on a single partition
- Global indexes on multiple partitions
OceanBase Database does not support the use of parallel DML for the following SQL statements:
REPLACEINSERT INTO ON DUPLICATE KEY UPDATE- Multi-table DML statements
If any of the following attributes exist on the table, parallel DML is not supported:
- Triggers
- Foreign keys
CHECKconstraints- Unique indexes