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.

Continue Reading