INSERT Concurrent DML with Column List
For starters, I'm on 11gR2
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
I am trying to parallelize using PARALLEL DML, a very large statement INSERT
that loads millions of rows. For this, I enable concurrent DML with ALTER SESSION ENABLE PARALLEL DML
. My insert looks like this:
INSERT INTO TAB_NEW (COL1, COL2, COL3, COL4, ...) --32 columns
SELECT COL1,
COL2,
COL3,
....
.... --32 columns selected
FROM TAB_A a, TAB_B b,...
WHERE ....; --A bunch of joins here
I am trying to do document level parallel to DML, for example:
Attempt 1 -
INSERT /*+ PARALLEL(16) */ TAB_NEW (COL1, COL2, COL3, COL4, ...) --32 columns
SELECT COL1,
COL2,
COL3,
....
.... --32 columns selected
FROM TAB_A a, TAB_B b,...
WHERE ....; --A bunch of joins here
However, the above does not work in parallel. Following is given below -
Attempt 2 -
INSERT /*+ PARALLEL(16) */ TAB_NEW (COL1, COL2, COL3, COL4, ...) --32 columns
SELECT /*+ PARALLEL(16) */
COL1,
COL2,
COL3,
....
.... --32 columns selected
FROM TAB_A a, TAB_B b,...
WHERE ....; --A bunch of joins here
OR
Attempt 3 -
INSERT /*+ PARALLEL(16) */ INTO TAB_NEW
SELECT COL1,
COL2,
COL3,
....
.... --32 columns selected
FROM TAB_A a, TAB_B b,...
WHERE ....; --A bunch of joins here
Is this happening because of the list of columns in the statement INSERT
in attempt 1?
source to share
Therefore, the reason parallel DML will not execute the query in PARALLEL is because a TAB_NEW
trigger was included in the table that was included. There is a limitation on PARALLEL DML
, and any concurrent DML attempt on this table will only run after the trigger. When the trigger was disabled, INSERT
executed in parallel.
A complete list of restrictions can be found here .
source to share
This works with the simple example below on both 11.2.0.3 (EE, 64bit, Solaris) and 12.1.0.1 (EE, 64bit, Windows).
This means that you are facing a very specific error that could be related to some minor details of your facility, operator or environment. The next step is the painful process of changing your request until it matches my request, looking for trivial differences that make it stop working.
Examples of tables
--drop table tab_new;
--drop table tab_a;
--drop table tab_b;
--drop table tab_c;
create table tab_new(col01 number,col02 number,col03 number,col04 number,col05 number,col06 number,col07 number,col08 number,col09 number,col10 number,col11 number,col12 number,col13 number,col14 number,col15 number,col16 number,col17 number,col18 number,col19 number,col20 number,col21 number,col22 number,col23 number,col24 number,col25 number,col26 number,col27 number,col28 number,col29 number,col30 number,col31 number,col32 number) nologging;
create table tab_a (col01 number,col02 number,col03 number,col04 number,col05 number,col06 number,col07 number,col08 number,col09 number,col10 number,col11 number,col12 number,col13 number,col14 number,col15 number,col16 number,col17 number,col18 number,col19 number,col20 number,col21 number,col22 number,col23 number,col24 number,col25 number,col26 number,col27 number,col28 number,col29 number,col30 number,col31 number,col32 number) nologging;
create table tab_b (col01 number,col02 number,col03 number,col04 number,col05 number,col06 number,col07 number,col08 number,col09 number,col10 number,col11 number,col12 number,col13 number,col14 number,col15 number,col16 number,col17 number,col18 number,col19 number,col20 number,col21 number,col22 number,col23 number,col24 number,col25 number,col26 number,col27 number,col28 number,col29 number,col30 number,col31 number,col32 number) nologging;
create table tab_c (col01 number,col02 number,col03 number,col04 number,col05 number,col06 number,col07 number,col08 number,col09 number,col10 number,col11 number,col12 number,col13 number,col14 number,col15 number,col16 number,col17 number,col18 number,col19 number,col20 number,col21 number,col22 number,col23 number,col24 number,col25 number,col26 number,col27 number,col28 number,col29 number,col30 number,col31 number,col32 number) nologging;
Statements
rollback;
alter session enable parallel dml;
explain plan for
INSERT /*+ PARALLEL(16) */ INTO TAB_NEW (col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32)
SELECT a.col01,a.col02,a.col03,a.col04,a.col05,a.col06,a.col07,a.col08,a.col09,a.col10,a.col11,a.col12,a.col13,a.col14,a.col15,a.col16,a.col17,a.col18,a.col19,a.col20,a.col21,a.col22,a.col23,a.col24,a.col25,a.col26,a.col27,a.col28,a.col29,a.col30,a.col31,a.col32
FROM TAB_A a, TAB_B b, TAB_C c
WHERE a.col01 = b.col01
AND b.col01 = c.col01;
select * from table(dbms_xplan.display(format => 'basic'));
Explain the plan
An operation LOAD AS SELECT
and its location under the operation PX
means that the data is inserted in parallel.
Plan hash value: 1632580283
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | INSERT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 |
| 3 | LOAD AS SELECT | TAB_NEW |
| 4 | OPTIMIZER STATISTICS GATHERING | |
| 5 | HASH JOIN | |
| 6 | HASH JOIN | |
| 7 | PX RECEIVE | |
| 8 | PX SEND HASH | :TQ10000 |
| 9 | PX BLOCK ITERATOR | |
| 10 | TABLE ACCESS FULL | TAB_A |
| 11 | PX RECEIVE | |
| 12 | PX SEND HASH | :TQ10001 |
| 13 | PX BLOCK ITERATOR | |
| 14 | TABLE ACCESS FULL | TAB_B |
| 15 | PX RECEIVE | |
| 16 | PX SEND HASH | :TQ10002 |
| 17 | PX BLOCK ITERATOR | |
| 18 | TABLE ACCESS FULL | TAB_C |
-------------------------------------------------------
source to share