Oracle Database Administration: The Essential Refe

Parallel operation hints

Hints in this category allow you to specify the degree of parallelism used in the query. These hints would override any PARALLEL values in the INIT.ORA file or specified at the table level. The following list describes each hint in this category and provides an example of its use:

PARALLEL

Overrides the default parallelism values that would normally be used for a table. This hint has three parameters: the table name, the degree of parallelism, and an optional third parameter that specifies the number of instances in a parallel server that can be used.

SELECT /*+ FULL(emp) PARALLEL(emp,4,4) */ ename
FROM emp;

NOPARALLEL

Overrides the default parallelism value for a table to preclude the use of a parallel query.

SELECT /*+ NOPARALLEL(emp) */  ename
FROM emp;

Note that this is the same as:

SELECT /*+ PARALLEL(emp,1,1) */ ename
FROM emp;

APPEND

This hint is used for INSERTs. With the hint, data is appended to the end of the table, and unused free space in data blocks is ignored. This is the default mode for parallel INSERTs in Oracle8.

INSERT /*+ APPEND */ 
INTO emp
SELECT * from scott.emp;

NOAPPEND

This hint is used for INSERTs to override APPEND mode. In this case, all available free space in the data blocks is used first before appending to the end of the table.

INSERT /*+ NOAPPEND */
INTO emp
SELECT * FROM scott.emp;

PARALLEL_INDEX

This hint is similar to the PARALLEL hint. It allows you to override the normal parallelism values for parallel index scans for partitioned indexes. ...