Parallel DML uses a parallel execution mechanism to improve the efficiency of inserting, updating, and deleting operations on tables and indexes in large databases. For decision support system (DSS) databases, parallel DML provides query and update capabilities, complementing parallel query functionality. For OLTP databases, parallel DML operations can accelerate the execution of batch processing jobs.
Enable and disable parallel DML
OceanBase AI Database supports explicitly enabling parallel DML in SQL statements or 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) */
Generally, 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.
The following example uses both the ENABLE_PARALLEL_DML and PARALLEL(n) hints 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 a session
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 to enable parallel DML in a session is:
SET _FORCE_PARALLEL_DML_DOP = n;
where n is greater than 1.
To disable parallel DML, execute the following SQL statement:
SET _FORCE_PARALLEL_DML_DOP = 1;
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.
Support for partition-wise parallel DML
Here is an example of the partition-wise parallel DML feature.
Create the test table
branch_sp_tbl_src.CREATE TABLE branch_sp_tbl_src(id INT PRIMARY KEY, v INT) PARTITION BY KEY(id) PARTITIONS 4;Create the test table
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 show the execution plan of an INSERT operation.
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 operators in the query plan are analyzed as follows:
- Operator 0: a parallel execution coordinator for managing parallel execution processes.
- Operator 1: distributes data among different execution nodes.
- Operator 2: traverses partitions for the query.
partition wiseindicates that the query intelligently processes data across partitions. - Operator 3: an
INSERTorUPDATEoperation. If the key to be inserted does not exist in the destination table, the database performs anINSERToperation. Otherwise, the database performs anUPDATEoperation. - Operators 4 and 5: perform a full scan on the
branch_sp_tbl_srctable. This table is the data source from which data is selected for theINSERToperation.
Usage instructions
OceanBase AI Database supports parallel execution for the following SQL statements:
INSERT INTO SELECTUPDATEDELETE
Parallel execution is supported if the table has any of the following types of indexes:
- Local indexes
- Single-partition global indexes
- Multi-partition global indexes
Supported scenarios for parallel DML
Scenario |
INSERT |
UPDATE |
DELETE |
MERGE INTO |
|---|---|---|---|---|
The table has foreign keys, triggers, PL/UDFs, or unique indexes.
NoteWhether a trigger is fired cannot be determined: if a trigger is unrelated to the DML operation, it will not be fired, and parallel DML will proceed. For example, operations such as |
Not supported | Not supported | Not supported | Not supported |
| Multi-table DML | INSERT ALL syntax is not supported for parallel DML. |
Parallel UPDATE on joined tables is supported. |
Same as UPDATE |
Syntax not supported |
| Auto-increment column | Parallel DML is supported in some scenarios. For details, see the examples below. | Supported | Supported | Supported (seq) |
| ArrayBinding batch optimization | Not supported | Not supported | Not supported | Not supported |
| Use USER_VARIABLE | Not supported | Not supported | Not supported | Not supported |
| IGNORE | Not supported | Not supported | Not supported | Syntax not supported |
| DBLink | Not supported | Not supported | Not supported | Not supported |
| Others | The following statements do not support parallel DML:
|
Columns that contain ON UPDATE CURRENT_TIMESTAMP are not supported. |
When writing to a table with auto-increment columns using INSERT, parallel DML is not supported if the auto-increment column is the primary key or partitioning key, and that column is specified.
Example:
Create table
tbl1, where columncol1is an auto-increment column and is specified as the primary key.CREATE TABLE tbl1(col1 INT AUTO_INCREMENT PRIMARY KEY, col2 INT, col3 INT);Create table
tbl2, where columncol1is an auto-increment column.CREATE TABLE tbl1(col1 INT AUTO_INCREMENT, col2 INT, col3 INT);Create table
tbl3.CREATE TABLE tbl3(col1 INT, col2 INT, col3 INT);When writing data via
INSERT, since the auto-increment columncol1is the primary key, parallel DML is not supported.INSERT /*+enable_parallel_dml parallel(3)*/ INTO tbl1 SELECT * FROM tbl3;When writing data via
INSERT, since columncol1is not specified, parallel DML is supported.INSERT /*+enable_parallel_dml parallel(3)*/ INTO tbl1(col2, col3) SELECT col2, col3 FROM tbl3;When writing data via
INSERT, since columncol1is only an auto-increment column, parallel DML is supported.INSERT /*+enable_parallel_dml parallel(3)*/ INTO tbl2 SELECT * FROM tbl3;
Parallel DML with correlated updates
Correlated updates refer to using multi-table join operations in an UPDATE statement to update a target table based on data from related tables. With the parallel DML feature, you can significantly improve the performance of large-scale data update operations.
Example:
The following steps demonstrate how to implement correlated updates using parallel DML.
Create two test tables
customersandorders.CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(50), customer_level VARCHAR(10) );CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, discount DECIMAL(3, 2), amount DECIMAL(10, 2) );Insert test data.
Insert customer data into the
customerstable:INSERT INTO customers (id, name, customer_level) VALUES (1, 'Alice', 'VIP'), (2, 'Bob', 'NORMAL'), (3, 'Charlie', 'OTHER');Insert order data into the
orderstable:INSERT INTO orders (order_id, customer_id, discount, amount) VALUES (101, 1, NULL, 1000.00), (102, 2, NULL, 2000.00), (103, 3, NULL, 1500.00), (104, 1, NULL, 3000.00), (105, 2, NULL, 2500.00);
Perform parallel DML with correlated updates.
Use
EXPLAINto view the execution plan for the parallel DML correlated update: specify the degree of parallelism as 4 using thePARALLELhint, and update thediscountcolumn in theorderstable based on the customer level (customer_level) in thecustomerstable.EXPLAIN UPDATE /*+ PARALLEL(4) */ orders o JOIN customers c ON o.customer_id = c.id SET o.discount = CASE WHEN c.customer_level = 'VIP' THEN 0.9 WHEN c.customer_level = 'NORMAL' THEN 0.95 ELSE 1.0 END;The return result is as follows:
+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------- | | |0 |DISTRIBUTED UPDATE | |3 |50 | | | |1 |└─PX COORDINATOR | |3 |3 | | | |2 | └─EXCHANGE OUT DISTR |:EX10001|3 |3 | | | |3 | └─SHARED HASH JOIN | |3 |3 | | | |4 | ├─EXCHANGE IN DISTR | |3 |2 | | | |5 | │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000|3 |2 | | | |6 | │ └─PX BLOCK ITERATOR | |3 |1 | | | |7 | │ └─TABLE FULL SCAN |c |3 |1 | | | |8 | └─PX BLOCK ITERATOR | |5 |1 | | | |9 | └─TABLE FULL SCAN |o |5 |1 | | | ========================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | table_columns([{o: ({orders: (o.order_id, o.customer_id, o.discount, o.amount)})}]), | | update([o.discount=column_conv(DECIMAL_INT,PS:(3,2),NULL,CASE WHEN c.customer_level = 'VIP' THEN cast(0.9, DECIMAL_INT(3, 2)) WHEN c.customer_level | | = 'NORMAL' THEN 0.95 ELSE cast(1.0, DECIMAL_INT(3, 2)) END)]) | | 1 - output([o.order_id], [o.customer_id], [o.discount], [o.amount], [c.customer_level]), filter(nil), rowset=16 | | 2 - output([o.order_id], [o.customer_id], [o.discount], [o.amount], [c.customer_level]), filter(nil), rowset=16 | | dop=4 | | 3 - output([o.order_id], [o.customer_id], [o.discount], [o.amount], [c.customer_level]), filter(nil), rowset=16 | | equal_conds([o.customer_id = c.id]), other_conds(nil) | | 4 - output([c.id], [c.customer_level]), filter(nil), rowset=16 | | 5 - output([c.id], [c.customer_level]), filter(nil), rowset=16 | | dop=4 | | 6 - output([c.id], [c.customer_level]), filter(nil), rowset=16 | | 7 - output([c.id], [c.customer_level]), filter(nil), rowset=16 | | access([c.id], [c.customer_level]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([c.id]), range(MIN ; MAX)always true | | 8 - output([o.order_id], [o.customer_id], [o.discount], [o.amount]), filter(nil), rowset=16 | | 9 - output([o.order_id], [o.customer_id], [o.discount], [o.amount]), filter(nil), rowset=16 | | access([o.order_id], [o.customer_id], [o.discount], [o.amount]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([o.order_id]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 38 rows in set