Skip to Main Content
  • Questions

  • Regarding Parallel DML and Usage of Hints

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Hariharan.

Asked: September 01, 2009 - 4:43 am UTC

Last updated: July 01, 2013 - 7:08 pm UTC

Version: 10.0.2

Viewed 1000+ times

You Asked

Hi Tom,

Good Day.

When I was about to tune an application in the current company, I came across this statement in AWR report.

INSERT /*+ PARALLEL */ INTO TRADES VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 , :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 , :B24 , :B25 , :B26 , :B27 , :B28 , :B29 , :B30 , :B31 , :B32 , :B33 , :B34 , :B35 , :B36 , :B37 , :B38 , :B39 , :B40 , :B41 , :B42 , :B43 , :B44 , :B45 , :B46 , :B47 , :B48 , :B49 , :B50 , :B51 , :B52 , :B53 , :B54 , :B55 , :B56 , :B57 , :B58 , :B59 , :B60 , :B61 , :B62 , :B63 , :B64 , :B65 , :B66 , :B67 )

Number of Executions: 1
Number of Gets: 323,173.00
Number of Reads: 14,865.00
Elapsed Per Exec (s): 1640.40

a) What is the advantage of using PDML in this statement? Will it be more beneficial if I remove the Parallel Hint?
b) Is it okay to induce usage of indexes as hints? For example, consider the following (please pardon me for the improper formatting):

SQL Query:

SELECT /*+ INDEX(B PK_BFDT) INDEX(A PK_DOMT) INDEX(D IDUPT01) INDEX(C IDOMT_L_02) */
A.DOC_ID ||' '||A.DOC_NME||' '||TO_CHAR(A.CRT_TS,'MM/DD/YYYY HH24:MI:SS')
||' '||TO_CHAR(A.LAST_UPDT_TS,'MM/DD/YYYY HH24:MI:SS') ||' '||A.DOCUMENTSTAT_DOCUMENTSTATU_FK
||' '||A.DOC_EXHAUST_IND||' '||A.DOCINPUTSOUR_DOCUMENTINPUT_FK||' '||A.INTRODUCINGBROKER_IBD_CODE_FK
||' '||A.DOC_IM_CAPTURE_BTCH_NM||' '||A.DOC_SPPR_IN||' '||A.NUM_OF_PG||' '||B.DOC_DESC||' '||B.DOC_TY
||' '||B.BUSINESSFUNC_BUSINESSFUNCT_FK||' '||A.STRG_PATH||' '||A.DOCUMENTFORMAT_FORMATCODE_FK||' '||A.OFC_ID
||' '||A.IP_ID||' '||A.DOC_PVT_IN||' '||A.DOC_PVT_USR_ID||' '||A.DOC_SIZE_BYTE_CT||' '||A.IM_DOC_VER_ENBL_IN
||' '||D.EXT_APPL_SYS_TRANS||' '||D.NTRSE_RQST_ID||' '||D.EXC_ITM_ID||' ' AS RESULT FROM
BFDT_BUS_FUNC_DOC_TY B,DUPT_DOC_UNDER_PROC D,DOMT_DOC A,DOMT_DOC_ACCOUNTS_L C WHERE C.TARG_ACCTNUM = '5NX758200'
AND C.BUS_FUNC_GRP_TX = 'Pershing' AND A.DOC_ID = C.SRC_DOC_ID AND A.BUS_FUNC_GRP_TX = 'Pershing' AND
A.DOCUMENTSTAT_DOCUMENTSTATU_FK IN ('51','50') AND A.DOC_EXHAUST_IND IS NULL AND
A.BUSINESSFUNCDOCTY_OBJECTID_FK IN (1441,1534) AND B.OBJ_ID = A.BUSINESSFUNCDOCTY_OBJECTID_FK AND
D.DOC_ID(+) = A.DOC_ID ORDER BY A.CRT_TS DESC

Execution Plan
----------------------------------------------------------
Plan hash value: 1105676031
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 213 | 26 (4)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 213 | 26 (4)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 213 | 25 (0)| 00:00:01 | | |
|* 3 | HASH JOIN | | 1 | 168 | 23 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 8 | 1048 | 20 (0)| 00:00:01 | | |
| 5 | PARTITION LIST SINGLE | | 8 | 200 | 7 (0)| 00:00:01 | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| DOMT_DOC_ACCOUNTS_L | 8 | 200 | 7 (0)| 00:00:01 | 1 | 1 |
|* 7 | INDEX RANGE SCAN | IDOMT_L_02 | 8 | | 3 (0)| 00:00:01 | 1 | 1 |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| DOMT_DOC | 1 | 106 | 2 (0)| 00:00:01 | 1 | 1 |
|* 9 | INDEX UNIQUE SCAN | PK_DOMT | 1 | | 1 (0)| 00:00:01 | | |
| 10 | INLIST ITERATOR | | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | BFDT_BUS_FUNC_DOC_TY | 2 | 74 | 3 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | PK_BFDT | 2 | | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | DUPT_DOC_UNDER_PROC | 1 | 45 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | IDUPT01 | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJ_ID"="A"."BUSINESSFUNCDOCTY_OBJECTID_FK")
7 - access("C"."BUS_FUNC_GRP_TX"='Pershing' AND "C"."TARG_ACCTNUM"='5NX758200')
8 - filter(("A"."BUSINESSFUNCDOCTY_OBJECTID_FK"=1441 OR "A"."BUSINESSFUNCDOCTY_OBJECTID_FK"=1534) AND
"A"."DOC_EXHAUST_IND" IS NULL AND ("A"."DOCUMENTSTAT_DOCUMENTSTATU_FK"='50' OR
"A"."DOCUMENTSTAT_DOCUMENTSTATU_FK"='51') AND "A"."BUS_FUNC_GRP_TX"='Pershing')
9 - access("A"."DOC_ID"="C"."SRC_DOC_ID")
12 - access("B"."OBJ_ID"=1441 OR "B"."OBJ_ID"=1534)
14 - access("D"."DOC_ID"(+)="A"."DOC_ID")

If I remove the hints and execute as follows:

SELECT A.DOC_ID ||' '||A.DOC_NME||' '||TO_CHAR(A.CRT_TS,'MM/DD/YYYY HH24:MI:SS')
||' '||TO_CHAR(A.LAST_UPDT_TS,'MM/DD/YYYY HH24:MI:SS') ||' '||A.DOCUMENTSTAT_DOCUMENTSTATU_FK
||' '||A.DOC_EXHAUST_IND||' '||A.DOCINPUTSOUR_DOCUMENTINPUT_FK||' '||A.INTRODUCINGBROKER_IBD_CODE_FK
||' '||A.DOC_IM_CAPTURE_BTCH_NM||' '||A.DOC_SPPR_IN||' '||A.NUM_OF_PG||' '||B.DOC_DESC||' '||B.DOC_TY
||' '||B.BUSINESSFUNC_BUSINESSFUNCT_FK||' '||A.STRG_PATH||' '||A.DOCUMENTFORMAT_FORMATCODE_FK||' '||A.OFC_ID
||' '||A.IP_ID||' '||A.DOC_PVT_IN||' '||A.DOC_PVT_USR_ID||' '||A.DOC_SIZE_BYTE_CT||' '||A.IM_DOC_VER_ENBL_IN
||' '||D.EXT_APPL_SYS_TRANS||' '||D.NTRSE_RQST_ID||' '||D.EXC_ITM_ID||' ' AS RESULT FROM
BFDT_BUS_FUNC_DOC_TY B,DUPT_DOC_UNDER_PROC D,DOMT_DOC A,DOMT_DOC_ACCOUNTS_L C WHERE C.TARG_ACCTNUM = '5NX758200'
AND C.BUS_FUNC_GRP_TX = 'Pershing' AND A.DOC_ID = C.SRC_DOC_ID AND A.BUS_FUNC_GRP_TX = 'Pershing' AND
A.DOCUMENTSTAT_DOCUMENTSTATU_FK IN ('51','50') AND A.DOC_EXHAUST_IND IS NULL AND
A.BUSINESSFUNCDOCTY_OBJECTID_FK IN (1441,1534) AND B.OBJ_ID = A.BUSINESSFUNCDOCTY_OBJECTID_FK AND
D.DOC_ID(+) = A.DOC_ID ORDER BY A.CRT_TS DESC
Execution Plan
----------------------------------------------------------
Plan hash value: 1105676031
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 213 | 26 (4)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 213 | 26 (4)| 00:00:01 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 213 | 25 (0)| 00:00:01 | | |
|* 3 | HASH JOIN | | 1 | 168 | 23 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 8 | 1048 | 20 (0)| 00:00:01 | | |
| 5 | PARTITION LIST SINGLE | | 8 | 200 | 7 (0)| 00:00:01 | KEY | KEY |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| DOMT_DOC_ACCOUNTS_L | 8 | 200 | 7 (0)| 00:00:01 | 1 | 1 |
|* 7 | INDEX RANGE SCAN | IDOMT_L_02 | 8 | | 3 (0)| 00:00:01 | 1 | 1 |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| DOMT_DOC | 1 | 106 | 2 (0)| 00:00:01 | 1 | 1 |
|* 9 | INDEX UNIQUE SCAN | PK_DOMT | 1 | | 1 (0)| 00:00:01 | | |
| 10 | INLIST ITERATOR | | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | BFDT_BUS_FUNC_DOC_TY | 2 | 74 | 3 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | PK_BFDT | 2 | | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | DUPT_DOC_UNDER_PROC | 1 | 45 | 2 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | IDUPT01 | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."OBJ_ID"="A"."BUSINESSFUNCDOCTY_OBJECTID_FK")
7 - access("C"."BUS_FUNC_GRP_TX"='Pershing' AND "C"."TARG_ACCTNUM"='5NX758200')
8 - filter(("A"."BUSINESSFUNCDOCTY_OBJECTID_FK"=1441 OR "A"."BUSINESSFUNCDOCTY_OBJECTID_FK"=1534) AND
"A"."DOC_EXHAUST_IND" IS NULL AND ("A"."DOCUMENTSTAT_DOCUMENTSTATU_FK"='50' OR
"A"."DOCUMENTSTAT_DOCUMENTSTATU_FK"='51') AND "A"."BUS_FUNC_GRP_TX"='Pershing')
9 - access("A"."DOC_ID"="C"."SRC_DOC_ID")
12 - access("B"."OBJ_ID"=1441 OR "B"."OBJ_ID"=1534)
14 - access("D"."DOC_ID"(+)="A"."DOC_ID")

Surprisingly, both execution plans are same and please note that the PLAN ID are also same. Optimizer mode is ALL_ROWS. Table and Index statistics are collected every day using DBMS_STATS

Could you please explain on why the optimizer is choosing the same plan for both the statements? Also, as you have suggested that using hints should be avoided, in this case, what should I do?

Well, I have posted two questions. I will stop with this and post the remaining questions later.

Thanks

Hari

and Tom said...

... INSERT /*+ PARALLEL */ INTO TRADES VALUES (:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , .... :B67 ) ...

could you tell me the logic behind even trying to use (it does not work, thankfully) parallel insert with a insert VALUES statement????


a) What is the advantage of using PDML in this statement? Will it be more beneficial if I remove the Parallel Hint?
b) Is it okay to induce usage of indexes as hints? For example, consider the following (please pardon me for the improper formatting):

a) none, it is a single row insert - insert values is - it would make no sense to execute it in parallel.


b) usually not, only if there is a bug - and upon the next patch we would re-evaluate our use of it.


Surprisingly, both execution plans are same and please note that the PLAN ID are also same. Optimizer mode is ALL_ROWS. Table and Index statistics are collected every day using DBMS_STATS

why is that surprising? It just means "optimizer did not need your inputs". The sql ids would be different for these, but since they have the same plan - you would expect the plan hash to be the same (same data = same hash values)

Could you please explain on why the optimizer is choosing the same plan for both the statements?


because your hints happend to coincide with what the optimizer decided to do? I'm not really sure why this is a surprise. We got the same plans - so?



Also, as you have suggested that using hints should be avoided, in this case, what should I do?


Listen to me? Lose the hints.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Regarding Parallel DML and Usage of Hints

Hariharan Sairam Titai, September 07, 2009 - 4:22 am UTC

Hi Tom,

With reference to the preceding comments, provided by you, I removed the Parallel Hint from the Insert statement and executed. I got the following (please pardon me for improper formatting):

/* Insert With Parallel Hint */

INSERT /*+ PARALLEL(STG_POSITIONS, 4) */ INTO POSITIONS
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 9.61 9.45 2 1964 200858 56717
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 9.61 9.45 2 1964 200858 56717

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS

/* Insert Without Parallel Hint */

INSERT INTO POSITIONS
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 57 9.98 12.74 0 1983 200580 56717
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 9.98 12.75 0 1983 200580 56717

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS

Since, I do not have access to the production database, I am not able to give you the statistics details.

I would like to know the following:

a) When executed without the hint, "Execute" was observed to increase from 1 to 57.
b) "elapsed" increased from 9.45 to 12.75
c) "query" increased from 1964 to 1983.

I am not able to find out the reason of why these values are increased.

Can you please provide me your valuable comments?

Thanks again

Hari
Tom Kyte
September 07, 2009 - 7:02 am UTC

a) you control that (the executions), your programmers tell us when and how often to execute the insert.

parallel in that context did nothing, there is/was nothing that could be parallelized. taking that hint away changed NOTHING, I told you to take it away because it was MISLEADING, erroneous, not happening, makes you look bad.

It would not be a good practice to array insert 50,000 plus records at a time, that is way too large, the second run looks *much* better. It is inserting about 10 times more records per execute than I would normally recommend (100 rows per batch is sort of a 'sweet spot')

b) and if you run it again, it'll be a different number, it just means you were waiting for something - unless you trace with waits enabled (dbms_monitor( ... waits => true ) or 10046 level 12 trace) you won't know what for - it could also be for cpu (eg: it took 12 seconds to get 9 cpu seconds)

this is a red herring, run again and it'll have a different elapsed, that is totally expected

c) big deal. 19 blocks? so?


Regarding Parallel DML and Usage of Hints

Gary Lively, May 20, 2010 - 11:11 am UTC

The tone of the responses was very disrespectful - we are your customers guys!

The information that followed was terse and not very complete.
Tom Kyte
May 24, 2010 - 12:09 pm UTC

how so?

I re-read it, I don't think it was in any way disrespectful.

How about you point out an explicit "for example"

Parallel Update

Snehasish Das, February 02, 2011 - 2:44 pm UTC

Hi Tom,

I read from below link that only when table is partitioned then only can the operations can be paralleled. I am not sure if this is true.

http://www.akadia.com/services/ora_parallel_processing.html

Can you please give your expert advice.

Regards,
Snehasish Das.
Tom Kyte
February 03, 2011 - 2:21 pm UTC

I would avoid that site - the material on it is very "dated" BUT NOT DATED.

Meaning - it is old, much of it is "borrowed" material, as in copied from other sites (I've seen my stuff show up there with a global change of ops$tkyte to SCOTT or whatever - often), and none of it has a timestamp or version information associated with it.

When reading material on the web look for three things:

a) a date timestamp as to when the article was published.
b) version information - information regarding what version of the software the article applies to.
c) the ability to provide feedback, ask a question, question the article itself.

If you don't see that - find stuff elsewhere.


The documentation easily refutes what they wrote there - parallel DML has been able to be executed against non-partitioned segments for quite a few releases.

http://docs.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel003.htm#CACEJACE

Since 9iR2 in fact.

Parallel Update.

Snehasish Das, February 03, 2011 - 11:29 pm UTC

Hi Tom,

I think i missed the word Update in the above question. I am sure we can do parallel Inserts in a non partitioned table. But i am not so sure about update. Can you put some light on below.
/*
Rules for UPDATE, MERGE, and DELETE
UPDATE, MERGE, and DELETE operations are parallelized by partition or subpartition. Update, merge, and delete parallelism are not possible within a partition, nor on a nonpartitioned table. See "Limitation on the Degree of Parallelism" for a possible restriction.
*/
In the 10.2 manual.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#i1006712

Apologies If i understood it wrong.
Thanks and Regards,
Snehasish Das.
Tom Kyte
February 04, 2011 - 9:26 am UTC

I don't know what you mean "i missed the word update".

The link I pointed you to includes:

Restrictions on Parallel DML

The following restrictions apply to parallel DML (including direct-path INSERT):

Intra-partition parallelism for UPDATE, MERGE, and DELETE operations require that the COMPATIBLE initialization parameter be set to 9.2 or greater



it is talking directly about updates - intra is "inner" or "the same" - single partition parallelism is possible.

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/whatsnew.htm#967650 



Parallel DML on Non-Partitioned Tables

You can now use parallel DML on non-partitioned tables.

See Also:
Chapter 21, "Using Parallel Execution"



It was a new 9i feature to enable PDML of all types on non-partitioned tables.


Your link sees to be pointing to an obsolete bit of text that needs to be removed. It was corrected in the 11g documentation set

http://docs.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel003.htm#sthref1080

Thanks

Snehasish Das, February 04, 2011 - 11:34 am UTC

Hi Tom,

As usual thanks a lot.

Regards,
Snehasish Das.

Parallel Update

lelio, April 26, 2011 - 11:57 am UTC

You said: It was corrected in the 11g documentation set
But at http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel003.htm#CACCAGCH
is written: Parallel UPDATE or DELETE (partitioned table only)

It is another bit of obsolete text?

I'm trying to do a parallel MERGE (only updating from a correlated query, no insert) on 10million records of a big table (18GB with 38million total records and 63 indexes), but the explain plan always show a serial merge.

The reason can be the cost of indexes maintenance?

I read on index maintenance for parallel merge here: http://dioncho.wordpress.com/2010/12/10/interpreting-parallel-merge-statement/

Can you suggest a better way to do the update? (the table is in use and must be available for read and some write during the update/merge)
Tom Kyte
April 26, 2011 - 2:48 pm UTC

looks like they missed a spot in the documentation.


Yes, it is a bit of obsolete text.

63 indexes!!!!!!!!!!!!!!!!!!!!!! not a record, but way way way up there.


Yes, that would tend to affect things in a huge way.


You probably want to skip the merge, do a parallel create table as select to recreate this data (emulate what the merge would have done in a SELECT statement) and then add back some of the indexes (I have to think that some of them are probably not necessary if you look at them all)



OR, if you cannot take the down time, be prepared for it to run for a very very long time. Maybe two orders of magnitude longer then it would if the indexes did not exist.

Parallel Update

lelio, April 26, 2011 - 9:44 pm UTC

Hi Tom,
yes we cannot take the down time, so not having the time to investigate the reason way this merge isn't executed in parallel dml, I executed the merge statement during the night (when the DB isn't used, too much) using only the parallel query.
With the greatest degree of parallelism (cpu_count*parallel_threads_per_cpu=36*2=72), Oracle spawned 143 sessions that have completed the update in 1:21:34 (h:mi:ss).

After the update was done and after a lot of work I discovered that the DML wasn't executed in parallel because the table to be updated have an "after update for each row" trigger (triggers are one of the restrictions that forbid parallel DML: http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel003.htm#CACEJACE ).
Another reason to not use triggers.

P.S.
During my research I found that some tables can have an attribute that forbid PARALLEL DML execution if not partitioned ( http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel007.htm#i1009083 )

I have also found another bit of obsolete text ( http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel007.htm#i1009319 ):
"You can then parallelize the update to further improve performance, but only if the customers table is partitioned"
And the update itself is missing the customers tables in the from clause.

Thanks again for your great web site.

Parallel DML on IOT

Rajeshwaran, June 26, 2013 - 10:11 am UTC

Tom:
I was reading your book Expert Oracle db architecture 11g. Page 633 (limitations associated with parallel dmls). Is that parallel DMLs are not supported on IOT's ? I ran the below script but I didn't get ORA-12838 (cannot read/modify an object after modifying it in parallel) error message. ( I am on 11.2.0.1 on Win 7 32-bit)

drop table t purge;
create table t
( x int, 
  y varchar2(30),
  z date,
  constraint t1_pk primary key(x)
 ) organization index parallel 4;
 
alter session enable parallel dml;
 
insert into t(x,y,z)
select rownum,object_name,sysdate
from all_objects;

select * from t where rownum = 1;

Tom Kyte
July 01, 2013 - 7:08 pm UTC

you cannot direct path load into an existing IOT. the query could run in parallel, but the load will be a conventional path one.

you can however do it via a create:

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cgcsp216pfjsd, child number 0
-------------------------------------
create table t ( x ,   y ,   z ,   constraint t1_pk primary key(x)  )
organization index parallel 4 as select rownum, object_name, sysdate
from stage

Plan hash value: 1427794727

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT     |          |       |       |    89 (100)|          |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ20001 | 74550 |  1237K|    80   (0)| 00:00:01 |  Q2,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT          |          |       |       |            |          |  Q2,01 | PCWP |            |
|   4 |     PX RECEIVE             |          | 74550 |  1237K|    80   (0)| 00:00:01 |  Q2,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN   | :TQ20000 | 74550 |  1237K|    80   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       COUNT                |          |       |       |            |          |        |      |            |
|   7 |        PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   8 |         PX SEND QC (RANDOM)| :TQ10000 | 74550 |  1237K|    80   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   9 |          PX BLOCK ITERATOR |          | 74550 |  1237K|    80   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 10 |           TABLE ACCESS FULL| STAGE    | 74550 |  1237K|    80   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.