During the OCM Preparation Workshop today in Bucharest, we got into a discussion about parallel inserts vs. direct load with append hint, where I said something like: „I think a parallel insert is always* also a direct load.“ Most attendees were satisfied with that, but some still looked a bit sceptical. And I was also not 100% sure about it. While the others practiced, I did a brief research and came up with this answer from Tom Kyte and quoted him: „Parallel is always a direct path, if you go parallel, you will be appending.“ Everybody in the room was immediately convinced – only I felt a bit uncomfortable with the outcome myself. After all, I don’t want to teach people to blindly follow my or any other guys claims without checking it if possible. And as with so many other claims about Oracle, it is possible to prove (or falsify) it with little effort:
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table t as select 'TEST' as col1 from dual connect by level<=1e6;
Table created.
SQL> select bytes/1024/1024 from user_segments;
BYTES/1024/1024
---------------
13
SQL> delete from t;
1000000 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from user_segments;
BYTES/1024/1024
---------------
13
The above shows that a conventional insert reuses the empty space before the high water mark of the table and does not allocate additional extents. Now I’m going to do a parallel insert:
SQL> delete from t;
1000000 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table t parallel;
Table altered.
SQL> alter session enable parallel dml;
Session altered.
SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from user_segments;
BYTES/1024/1024
---------------
24.375
QED! Obviously, the parallel insert appended rows after the high water mark – it was a direct load, in other words. That is in itself not so spectacular, but that little anecdote illustrates the in my opinion proper way to deal with technical questions about Oracle: Always try to back up your assumptions respectively claims with proof instead of relying only on (your or others) authority.
In other words: Don’t believe it, test it! 🙂
*Addendum: Never say always, I always say: If you include the NOAPPEND hint to the parallel insert, it will not be done as a direct load. Thank you guys for pointing that out!
Pickleball spielen 
#1 von Ivica Arsov am April 2, 2014 - 19:33
Nice post.
Direct-path is default in parallel mode.
From the docs:
„Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.“
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50901
#2 von hourim am April 2, 2014 - 19:57
Uwe,
A recent discussion on a similar question raised via an otn thread prompted me to write the following blog article
http://hourim.wordpress.com/2014/03/14/parallel-insert/
Best regards
Mohamed Houri
http://www.hourim.wordpress.com
#3 von Jure Bratina am April 2, 2014 - 22:58
Hi Uwe,
just a thought, another quick way to verify this could probably be a parallel insert (without commit) followed by a select on the same table and if an „ORA-12838: cannot read/modify an object after modifying it in parallel“ error is returned it can be implied that direct path was used.
Regards
#4 von Uwe Hesse am April 3, 2014 - 09:50
Ivica, thanks for your comment! Another reason why you should never say always – which is what I always say 😉
#5 von Uwe Hesse am April 3, 2014 - 09:54
Jure, I do this indeed after a parallel DML to show that it was actually done in parallel – which requires ALTER SESSION ENABLE PARALLEL. On the other hand, this message doesn’t say it was a direct load. And it is even a bit odd, in my opinion, that you get if after a serial direct load also.
#6 von Uwe Hesse am April 3, 2014 - 11:54
Mohammed Houri, your comment was for some reason captured by the anti-spam mechanism of WordPress, sorry for that. Thank you for pointing to your researches 🙂
#7 von Bruno Nascimento am April 3, 2014 - 21:08
I’m going to Bucharest to get the next OCM Preparation Workshop. My class will start 07/04.
#8 von Uwe Hesse am April 4, 2014 - 09:05
Bruno Nascimento, I will deliver that one too – so see you next week 🙂