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 hints 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 and PARALLEL(n) hints are used to set the DOP (n) to 2, meeting the requirement of n > 1.
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, 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 DML 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.
The syntax for enabling parallel DML in a session in MySQL mode is as follows:
SET _FORCE_PARALLEL_DML_DOP = n;
n is greater than 1.
The syntax for enabling 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 SQL 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 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. Here is an example 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 /*+ 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 in MySQL mode, execute the following SQL statement:
SET _FORCE_PARALLEL_DML_DOP = 1;
To disable parallel DML in Oracle mode, 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 limitations on parallel operations are violated, parallel DML does not take effect even if it is enabled.
Considerations
OceanBase Database supports parallel DML for the following SQL statements:
INSERT INTO SELECTUPDATEDELETE
If a table has the following types of indexes, you need to enable parallel DML:
- Local indexes
- Single-partition global indexes
- Multi-partition global indexes
OceanBase Database does not support parallel DML for the following SQL statements:
REPLACEINSERT INTO ON DUPLICATE KEY UPDATE- Multi-table DML statements
If a table has the following attributes, parallel DML is not supported:
- Triggers
- Foreign keys
CHECKconstraints- Unique indexes