Parallel DML Tip 1: INSERT

The functionality available using an INSERT statement can be summarized as shown in Table 8-5:

Table 8-5 Summary of INSERT Features

Insert Type Parallel Serial NOLOGGING

Conventional

No

See text in this section for information about using the NOAPPEND hint with parallel DML enabled to perform a parallel conventional insert.

Yes

No

Direct-path

INSERT

(APPEND)

Yes, but requires

ALTER SESSION ENABLE PARALLEL DML or the ENABLE_PARALLEL_DML SQL hint to enable PARALLEL DML mode

and one of the following:

  • Table PARALLEL attribute or PARALLEL hint to explicitly set parallelism

  • APPEND hint to explicitly set mode

Or the following

ALTER SESSION FORCE PARALLEL DML to force PARALLEL DML mode

Yes, but requires:

APPEND hint

Yes, but requires:

NOLOGGING attribute set for partition or table

If parallel DML is enabled and there is a PARALLEL hint or PARALLEL attribute set for the table in the data dictionary, then insert operations are parallel and appended, unless a restriction applies. If either the PARALLEL hint or PARALLEL attribute is missing, the insert operation is performed serially. Automatic DOP only parallelizes the DML part of a SQL statement if and only if parallel DML is enabled or forced.

If parallel DML is enabled, then you can use the NOAPPEND hint to perform a parallel conventional insert operation. For example, you can use /*+ noappend parallel */ with the SQL INSERT statement to perform a parallel conventional insert.

SQL> INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist SELECT * FROM sales;

The advantage of the parallel conventional insert operation is the ability to perform online operations with none of the restrictions of direct-path INSERT. The disadvantage of the parallel conventional insert operation is that this process may be slower than direct-path INSERT.