Enabling Parallelism for the table and Index in Oracle

Check the parallelism of the object or index with the following query:

-- Check degree of table
select degree from dba_tables where table_name = 'EMPLOYEES';
--Check degree of index
select index_name,degree from dba_indexes where table_name = 'EMPLOYEES';

Note: If automatic DOP is enabled (PARALLEL_DEGREE_POLICY=AUTO), then the parallelism that you set on objects is ignored.

Change the degree at the object level ( table or index):

 -- Enable parallel for Table:
 ALTER TABLE EMPLOYEES PARALLEL(DEGREE 4);

--Enable parallel for Index:
ALTER INDEX EMP_IDX PARALLEL(DEGREE 4);

-- Disable the parallel
ALTER TABLE EMPLOYEES PARALLEL(DEGREE 1);
ALTER TABLE EMPLOYEES NOPARALLEL;

Script to change parallel degree for all objects in Schema

-- For Tables
 select 'alter table '||owner||'."'||table_name||'" parallel (degree 4);' from dba_tables where owner='IC';

-- For indexes
select 'alter index '||owner||'."'||index_name||'" parallel (degree 4);' from dba_indexes where owner='IC';

Enable parallel at session-level forcefully:

 alter session force parallel query parallel N;
 --example: Run SQL Query with parallel with 4 process
 alter session force parallel query parallel 4;

You found a query running on a large table, you want to enable parallelism to check its speed after normal execution.

For using the parallelism on SQL Query, you need to explicitly define hints on the SQL Query for forcefully using the parallel operations.

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

Parallel hint works on tables as follows:
In the following queries, you specify the parallel 4 means 4 processes are used to fetch data from the employee’s table.

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

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

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

Parallel_index hints
Parallel_index hint is 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 a parallel number then oracle chooses automatically on DB settings.

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

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