Parallel DML uses parallel execution mechanisms to speed up or scale up INSERT, UPDATE, DELETE, and other operations on large database tables and indexes. For the database of a decision support system (DSS), parallel DML provides the query and update features as a supplement to parallel queries. For online transaction processing (OLTP) databases, parallel DML can speed up the execution of batch jobs.
Enable and disable parallel DML
OceanBase Database allows you to explicitly enable parallel DML in SQL statements and sessions.
Enable and disable parallel DML in SQL statements
To enable parallel DML in an SQL statement, insert the following hint into the statement:
/*+ ENABLE_PARALLEL_DML PARALLEL(3) */
In most cases, the ENABLE_PARALLEL_DML hint must be used together with the PARALLEL hint to enable parallel DML. However, if the table-level degree of parallelism (DOP) is specified on the schema of the target table, you only need to specify the ENABLE_PARALLEL_DML hint.
In the following example, both the ENABLE_PARALLEL_DML hint and the PARALLEL(n) parameter are used to specify the DOP (n), n > 1, and dop=2.
CREATE TABLE t1 (c1 NUMBER(38) PRIMARY KEY, c2 NUMBER(38)) NOPARALLEL;
CREATE TABLE t2 (c1 NUMBER(38) PRIMARY KEY, c2 NUMBER(38)) PARALLEL 11 PARTITION BY HASH(c1) PARTITIONS 3;
CREATE TABLE t3 (c1 NUMBER(38) PRIMARY KEY, c2 NUMBER(38)) PARALLEL 10 PARTITION BY HASH(c1) PARTITIONS 4;
obclient> EXPLAIN INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(2) */ INTO t1 SELECT * FROM T3\G
*************************** 1. row ***************************
==============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------------------
|0 |PX COORDINATOR | |400000 |537675|
|1 | EXCHANGE OUT DISTR |:EX10001|400000 |537675|
|2 | INSERT | |400000 |537675|
|3 | EXCHANGE IN DISTR | |400000 |482466|
|4 | EXCHANGE OUT DISTR (RANDOM)|:EX10000|400000 |302612|
|5 | SUBPLAN SCAN |VIEW1 |400000 |302612|
|6 | PX BLOCK ITERATOR | |400000 |247403|
|7 | TABLE SCAN |T3 |400000 |247403|
==============================================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil)
1 - output(nil), filter(nil), dop=2
2 - output(nil), filter(nil),
columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0), conv_exprs([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)])
3 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)]), filter(nil)
4 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)]), filter(nil), dop=2
5 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)]), filter(nil),
access([VIEW1.C1], [VIEW1.C2])
6 - output([T3.C1], [T3.C2]), filter(nil)
7 - output([T3.C1], [T3.C2]), filter(nil),
access([T3.C1], [T3.C2]), partitions(p[0-3])
1 row in set
To disable parallel DML, insert the following hint into the statement:
/*+ DISABLE_PARALLEL_DML */
Even if parallel DML is enabled in a session, you can still use the DISABLE_PARALLEL_DML hint to disable parallel PDML in a specified SQL statement.
Enable and disable parallel DML in sessions
By default, even if a PARALLEL hint is specified in an SQL statement, parallel DML is not enabled. You need to enable parallel DML in the session as well. To enable parallel DML in a session, execute the following SQL statement:
ALTER SESSION ENABLE PARALLEL DML;
To forcibly enable parallel DML in a session, execute the following SQL statement:
ALTER SESSION FORCE PARALLEL DML PARALLEL n;
The following example shows how to forcibly enable parallel DML in a session.
CREATE TABLE t1 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) NOPARALLEL;
CREATE TABLE t2 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) PARALLEL 11 PARTITION BY HASH(C1) PARTITIONS 3;
CREATE TABLE t3 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) 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\G
*************************** 1. row ***************************
===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------------
|0 |PX COORDINATOR | |400000 |537675|
|1 | EXCHANGE OUT DISTR |:EX10001|400000 |537675|
|2 | INSERT | |400000 |537675|
|3 | EXCHANGE IN DISTR | |400000 |482466|
|4 | EXCHANGE OUT DISTR (PKEY RANDOM)|:EX10000|400000 |302612|
|5 | SUBPLAN SCAN |VIEW1 |400000 |302612|
|6 | PX BLOCK ITERATOR | |400000 |247403|
|7 | TABLE SCAN |T3 |400000 |247403|
===================================================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil)
1 - output(nil), filter(nil), dop=6
2 - output(nil), filter(nil),
columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-2]), conv_exprs([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)])
3 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)], [PARTITION_ID]), filter(nil)
4 - (#keys=1, [column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)]), output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)], [PARTITION_ID]), filter(nil), dop=6
5 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)]), filter(nil),
access([VIEW1.C1], [VIEW1.C2])
6 - output([T3.C1], [T3.C2]), filter(nil)
7 - output([T3.C1], [T3.C2]), filter(nil),
access([T3.C1], [T3.C2]), partitions(p[0-3])
1 row in set
Note that when parallel DML is enabled in an SQL statement, queries are generally executed based on the DOP specified by the hint. The DOP specified by the hint has a higher priority than the one forcibly specified in the session. Sample code:
CREATE TABLE t1 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) NOPARALLEL;
CREATE TABLE t2 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) PARALLEL 11 PARTITION BY HASH(C1) PARTITIONS 3;
CREATE TABLE t3 (C1 NUMBER(38) PRIMARY KEY, C2 NUMBER(38)) 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\G
*************************** 1. row ***************************
===================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------------
|0 |PX COORDINATOR | |400000 |537675|
|1 | EXCHANGE OUT DISTR |:EX10001|400000 |537675|
|2 | INSERT | |400000 |537675|
|3 | EXCHANGE IN DISTR | |400000 |482466|
|4 | EXCHANGE OUT DISTR (PKEY RANDOM)|:EX10000|400000 |302612|
|5 | SUBPLAN SCAN |VIEW1 |400000 |302612|
|6 | PX BLOCK ITERATOR | |400000 |247403|
|7 | TABLE SCAN |T3 |400000 |247403|
===================================================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil)
1 - output(nil), filter(nil), dop=3
2 - output(nil), filter(nil),
columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-2]), conv_exprs([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)])
3 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)], [PARTITION_ID]), filter(nil)
4 - (#keys=1, [column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)]), output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)], [PARTITION_ID]), filter(nil), dop=3
5 - output([column_conv(NUMBER,PS:(38,0),NOT NULL,VIEW1.C1)], [column_conv(NUMBER,PS:(38,0),NULL,VIEW1.C2)]), filter(nil),
access([VIEW1.C1], [VIEW1.C2])
6 - output([T3.C1], [T3.C2]), filter(nil)
7 - output([T3.C1], [T3.C2]), filter(nil),
access([T3.C1], [T3.C2]), partitions(p[0-3])
1 row in set
To disable parallel DML, execute the following SQL statement:
ALTER SESSION DISABLE PARALLEL DML;
When parallel DML is disabled, parallel DML is not executed even if the PARALLEL hint is used in SQL statements. When parallel DML is enabled in a session, parallel execution is enabled for all DML statements in the session. If you use the ENABLE_PARALLEL_DML hint to enable parallel DML for an SQL statement, parallel execution is enabled only for the specified statement. However, if no table with parallel attributes exists, or the usage limits on parallel operations are violated, parallel DML does not take effect even if it is enabled.
Considerations
OceanBase Database supports parallel execution of the following SQL statements:
INSERT INTO SELECTUPDATEDELETE
If a table has the following types of indexes, you need to enable parallel execution:
- Local partitioned indexes
- Single-partition global indexes
- Multi-partition global indexes
OceanBase Database does not support parallel DML for the following SQL statements:
REPLACEMERGE INTOINSERT INTO ON DUPLICATE KEY UPDATE- Multi-table DML statements
If a table has the following attributes, parallel DML is not supported:
- Triggers
- Foreign keys
- CHECK constraints
- Unique partitioned indexes