Summary - Control on Parallel operation and Nologging in INSERT .. SELECT ..

Summary of Parallel operation in INSERT .. SELECT ..

1. PDML Mode and Insert Hint

<PDML statement>

alter session enable parallel dml;

--alter session disable parallel dml;

INSERT /*+ append parallel */ INTO TG_TB SELECT .. FROM SRC_TB;

PDML mode	hint on Insert	effect on Insert	effect on Select
OFF	none	conventional	serial
OFF	APPEND	serial direct-path	serial
OFF	PARALLEL	conventional	parallel
OFF	APPEND PARALLEL	serial direct-path	parallel
ON	none	conventional	serial
ON	APPEND	serial direct-path	serial
ON	PARALLEL	parallel direct-path	parallel
ON	APPEND PARALLEL	parallel direct-path	parallel

If you add APPEND with PARALLEL hint in PDML session, then APPEND is ignored.

But in the session PDML disabled, the PARALLEL hint not work on the insert operation.

Note. 

Test DBMS : 12c Enterprise Edition Release 12.1.0.1.0

2. NOLOGGING setting

NOLOGGING can be set only with ALTER TABLE statement. If you add 'NOLOGGING' as a option in INSERT statement like below, it is considered as a table alias - nologging not works.

       INSERT /*+APPEND */ INTO TG_TB NOLOGGING

       SELECT ...

<Setting NOLOGGING on a Table>

ALTER TABLE TG_TB NOLOGGING;

ALTER TABLE TG_TB LOGGING;

Insert Mode	DB Mode	테이블 Logging Option	Effect
Direct-path	NOARCHIVE	LOGGING	minimum redo
	NOARCHIVE	NOLOGGING	minimum redo
	ARCHIVE	LOGGING	small redo
	ARCHIVE	NOLOGGING	minimum redo
Conventional	all case	all case	full redo