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;

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;

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;

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;

This hint is similar to the PARALLEL hint. It allows you to override the normal parallelism values for parallel index scans for partitioned indexes. There are four parameters: the table name, the index name, the degree of parallelism, and the number of instances to use in a Parallel Server environment.

SELECT /*+ PARALLEL_INDEX (emp, emp_deptno, 4,4) */ ename
FROM emp
WHERE deptno = 10;

This hint is similar to the NOPARALLEL hint. It overrides the default parallelism values for a parallel index scan by forcing the optimizer not to use parallelism.

SELECT /*+ NO_PARALLEL_INDEX (emp, emp_deptno) */ ename
FROM emp
WHERE deptno = 10;

This is the same as specifying:

SELECT /*+ PARALLEL_INDEX (emp, emp_deptno, 1,1) */ ename
FROM emp
WHERE deptno = 10;