Use parallel hint in SQL query of Oracle

Use the parallel hint on SQL Query, you need to explicitly defines hints on the SQL Query for forcefully used the parallel operations.

Two way to be used parallel operation on SQL Query as
1. Parallel Hint
2. Parallel_index Hint

Parallel hint works on tables as follows:
In following queries, you are specify the parallel 4 means 4 process is used to fetch data from the employees table.


SELECT /*+ parallel(employees,4) */ emp_id, name FROM hr.employees;


In following queries, you specify nothing, Oracle detect the parallel operation based on database initialization parameters:


SELECT/*+ parallel(emp) */ emp_id, name FROM hr.employees emp;


Parallel_index hints
Parallel_index hint used for parallel access to indexes.


SELECT /*+ parallel_index(emp, emp_i4 ,4) */ emp_id, name
FROM employees WHERE deptno = 10;


Two arguments used in parallel_index hint: Table name and Index name. If you do not specify parallel number then oracle choose automatic on db settings.

NO_PARALLEL & NO_PARALLEL_INDEX Hints
Both used if you want to test the query without using parallel execution. Query is executed sometime better in case of non parallel then parallel
and your parallel level is set on table degree or instance level (PARALLEL_DEGREE_POLICY=AUTO)
and you want to over come this situation for specific query. Then use No_PARALLEL and NO_PARALLEL_INDEX.


SELECT/*+ NO_parallel(emp) */ emp_id, name FROM hr.employees emp;


Note: As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated. Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.