Here’s a fuzzy memory of mine: I thought I remembered reading where Tom Kyte once said “all parallel inserts are direct-path inserts.”
I don’t remember where I read it (I can’t even remember if that’s what was said, or if Tom said it). But today I wanted to see if that was true by playing around with APPEND and PARALLEL hints.
We have two different “places” we can hint.
- We can hint the insert.
insert /*+ hint goes here */ into ...
- We can hint the select.
select /*+ hint_goes_here */ ...
There are 4 different hints that can be placed in the insert (append, noappend, parallel, no_parallel).
There are 2 different hints that can be placed in the select clause (parallel, no_parallel).
Additionally, you can alter your session 3 different ways (enable parallel DML, disable parallel DML, force parallel DML).
So the number of possible scenarios we can make with all this mess:
- 3 possible ways to alter session
- 3 possible ways to hint the select (parallel, no_parallel, or no hint at all)
- 8 intelligent ways to hint the insert (no hint, append, parallel, noappend, noparallel, append + parallel, noappend + parallel, noappend + noparallel).
I specify “intelligent” because
/*+ append noappend */ doesn’t make sense; neither does
/*+ parallel noparallel */
8 x 3 x 3 = 72 different possible combinations. I don’t know that it makes sense to try them all, but I’ll highlight some points of interest.