INSERT Parallel DML with column list

Question

To start with, I am 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 attempting to parallelize, using PARALLEL DML, a very large INSERT statement which bulk loads millions of rows. To achieve this, I enable parallel DML by 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 Statement Level parallel DML, like so-

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 doesn't run in parallel. The one's below does-

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 due to the Column List in the INSERT statement in Attempt 1?

Answer 1

So the reason why the parallel DML won't execute the query in PARALLEL is because the table TAB_NEW had a trigger on it which was enabled. There is restriction on PARALLEL DML and any parallel DML attempt on that table will only run serial due to the trigger. Once the trigger was disabled the INSERT ran in parallel.

The full list of restrictions can be found here.

Answer 2

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 implies you are running into a very specific bug that may be related to some minor detail of your object, statement, or environment. Your next step is the painful process of modifying your query until it matches my query, looking for the trivial different that makes it stop working.

Sample 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 Plan

The LOAD AS SELECT operation and its location beneath a PX operation mean 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    |
-------------------------------------------------------