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 provides query and update features, complementing parallel query capabilities. In online transaction processing (OLTP) databases, parallel DML operations can accelerate batch processing jobs.
Enable or 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 SQL statements
To enable parallel DML in an SQL statement, add the following hint to the statement:
/*+ ENABLE_PARALLEL_DML PARALLEL(3) */
Generally, you must use the ENABLE_PARALLEL_DML hint and the PARALLEL hint together to enable parallel DML. However, if the target table has a schema that specifies the degree of parallelism at the table level, you need to use only the ENABLE_PARALLEL_DML hint.
The following example shows how to use the ENABLE_PARALLEL_DML hint and the PARALLEL(n) parameter to specify the degree of parallelism n and set n > 1. The degree of parallelism is 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
To disable parallel DML in an SQL statement, add the following hint to the statement:
/*+ DISABLE_PARALLEL_DML */
Even if parallel DML is enabled in a session, you can disable it for a specific SQL statement by adding the DISABLE_PARALLEL_DML hint to the statement.
Enable and disable parallel DML in sessions
By default, even if the PARALLEL hint is used in an SQL statement, parallel DML is disabled. Therefore, you must enable parallel DML in sessions.
To enable parallel DML in a session in MySQL mode, use the following syntax:
SET _FORCE_PARALLEL_DML_DOP = n;
where n is greater than 1.
To enable parallel DML in a session in Oracle mode, use the following syntax:
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 degree of parallelism specified in the statement takes precedence over the degree of parallelism forcibly specified in the session, especially in Oracle mode. The following example shows 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, use the following SQL statement:
SET _FORCE_PARALLEL_DML_DOP = 1;
To disable parallel DML in Oracle mode, use the following SQL statement:
ALTER SESSION DISABLE PARALLEL DML;
When parallel DML is disabled, even if the PARALLEL hint is used in an SQL statement, the DML operation is executed in series. When you enable parallel DML in a session, parallel execution applies to all DML statements in the session. If parallel DML is enabled by using the ENABLE_PARALLEL_DML hint, parallel execution applies only to the statement with the hint. However, if no table has the parallel attribute or the limitations on parallel operations are violated, DML operations are executed in series even if parallel DML is enabled.
Support for parallel processing in intervals
The following example describes how to use the table partitioning feature.
Create a test table named
branch_sp_tbl_src.CREATE TABLE branch_sp_tbl_src(id INT PRIMARY KEY, v INT) PARTITION BY KEY(id) PARTITIONS 4;Create a test table named
branch_sp_tbl_dest.CREATE TABLE branch_sp_tbl_dest LIKE branch_sp_tbl_src;View the execution plan.
Execute the following SQL statement to view how the insertion operation is performed.
obclient [test]> EXPLAIN BASIC INSERT /*+enable_parallel_dml parallel(100) query_timeout(1000000000)*/ INTO branch_sp_tbl_dest SELECT id, v FROM branch_sp_tbl_src ON DUPLICATE KEY UPDATE v = v + 1;The return result is as follows:
+-------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------+ | ================================================ | | |ID|OPERATOR |NAME | | | ------------------------------------------------ | | |0 |PX COORDINATOR | | | | |1 |└─EXCHANGE OUT DISTR |:EX10000 | | | |2 | └─PX PARTITION ITERATOR| | | | |3 | └─INSERT_UP | | | | |4 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1 | | | |5 | └─TABLE FULL SCAN|branch_sp_tbl_src| | | ================================================ | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil), rowset=16 | | 1 - output(nil), filter(nil), rowset=16 | | dop=100 | | 2 - output(nil), filter(nil), rowset=16 | | partition wise, force partition granule | | 3 - output(nil), filter(nil) | | columns([{branch_sp_tbl_dest: ({branch_sp_tbl_dest: (branch_sp_tbl_dest.id, branch_sp_tbl_dest.v)})}]), partitions(p[0-3]), | | column_values([column_conv(INT,PS:(11,0),NOT NULL,ANONYMOUS_VIEW1.id)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.v)]), | | update([branch_sp_tbl_dest.v=column_conv(INT,PS:(11,0),NULL,cast(branch_sp_tbl_dest.v + 1, INT(-1, 0)))]) | | 4 - output([ANONYMOUS_VIEW1.id], [ANONYMOUS_VIEW1.v]), filter(nil), rowset=16 | | access([ANONYMOUS_VIEW1.id], [ANONYMOUS_VIEW1.v]) | | 5 - output([branch_sp_tbl_src.id], [branch_sp_tbl_src.v]), filter(nil), rowset=16 | | access([branch_sp_tbl_src.id], [branch_sp_tbl_src.v]), partitions(p[0-3]) | | is_index_back=false, is_global_index=false, | | range_key([branch_sp_tbl_src.id]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------------------------------------------------------------------+ 27 rows in setThe query plan contains the following operators:
- Operator 0: This operator is a parallel execution coordinator that manages the processes of parallel execution.
- Operator 1: This operator distributes data across execution nodes.
- Operator 2: This operator indicates that the query traverses partitions. "Partition wise" means that the query intelligently processes data across partitions.
- Operator 3: This operator performs insert or update operations. If a key being inserted does not exist in the table, the system performs an insert operation; otherwise, the system performs an update operation.
- Operator 4 and 5: These operators perform a full table scan on the
branch_sp_tbl_srctable. This table is the data source from which data is selected for insertion.
Usage
OceanBase Database supports the parallel execution of the following SQL statements:
INSERT INTO SELECTUPDATEDELETE
If any of the following index types exist on a table, parallel execution must be supported:
- Local indexes
- Global indexes on a single partition
- Global indexes on multiple partitions
In OceanBase Database, for the following SQL statements, only specific types (namely, partition-wise types) of operations support parallel execution, while other types of operations do not support parallel DML:
REPLACEINSERT INTO ON DUPLICATE KEY UPDATE- Multi-table DML statements are not supported
If any of the following attributes exist on a table, parallel DML is not supported:
- Triggers
- Foreign keys
- Unique indexes