Parallelization Rules for SQL Statements

A SQL statement can be parallelized if it includes a parallel hint or if the table or index being operated on has been declared PARALLEL with a CREATE or ALTER statement.
In addition, a DDL statement can be parallelized by using the PARALLEL clause.
However, not all of these methods apply to all types of SQL statements.

Parallelization has two components: the decision to parallelize and the DOP. These
components are determined differently for queries, DDL operations, and DML
operations.

To determine the DOP, Oracle looks at the reference objects:

■ Parallel query looks at each table and index, in the portion of the query being
parallelized, to determine which is the reference table. The basic rule is to pick the
table or index with the largest DOP.
■ For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object
that determines the DOP is the table being modified by an insert, update, or delete
operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If
the parallel DML statement includes a subquery, the subquery's DOP is the same
as the DML operation.

■ For parallel DDL, the reference object that determines the DOP is the table, index,
or partition being created, rebuilt, split, or moved. If the parallel DDL statement
includes a subquery, the subquery's DOP is the same as the DDL operation
Rules for Parallelizing Queries
This section discusses some rules for parallelizing queries.
Decision to Parallelize A SELECT statement can be parallelized only if the following
conditions are satisfied:
■ The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX)
or the schema objects referred to in the query have a PARALLEL declaration
associated with them.
■ At least one of the tables specified in the query requires one of the following:
■ A full table scan
■ An index range scan spanning multiple partitions
■ No scalar subqueries are in the SELECT list.
Degree of Parallelism The DOP for a query is determined by the following rules:
■ The query uses the maximum DOP taken from all of the table declarations
involved in the query and all of the potential indexes that are candidates to satisfy
the query (the reference objects). That is, the table or index that has the greatest
DOP determines the query's DOP (maximum query directive).
■ If a table has both a parallel hint specification in the query and a parallel
declaration in its table specification, the hint specification takes precedence over
parallel declaration specification.

Rules for UPDATE, MERGE, and DELETE
UPDATE, MERGE, and DELETE operations are parallelized by partition or subpartition.
Update, merge, and delete parallelism are not possible within a partition, nor on a nonpartitioned table.


You have two ways to specify parallel directives for UPDATE, MERGE, and DELETE
operations (assuming that PARALLEL DML mode is enabled):

■ Use a parallel clause in the definition of the table being updated or deleted (the
reference object).
■ Use an update, merge, or delete parallel hint in the statement.

Parallel hints are placed immediately after the UPDATE, MERGE, or DELETE keywords
in UPDATE, MERGE, and DELETE statements. The hint also applies to the underlying
scan of the table being changed.

You can use the ALTER SESSION FORCE PARALLEL DML statement to override parallel
clauses for subsequent UPDATE, MERGE, and DELETE statements in a session. Parallel
hints in UPDATE, MERGE, and DELETE statements override the ALTER SESSION FORCE
PARALLEL DML statement.

Decision to Parallelize The following rule determines whether the UPDATE, MERGE, or
DELETE operation should be parallelized:

The UPDATE or DELETE operation will be parallelized if and only if at least one of the following is true:

■ The table being updated or deleted has a PARALLEL specification.
■ The PARALLEL hint is specified in the DML statement.
■ An ALTER SESSION FORCE PARALLEL DML statement has been issued previously
during the session.

If the statement contains subqueries or updatable views, then they may have their own
separate parallel hints or clauses. However, these parallel directives do not affect the decision to parallelize the UPDATE, MERGE, or DELETE.

The parallel hint or clause on the tables is used by both the query and the UPDATE,
MERGE, DELETE portions to determine parallelism, the decision to parallelize the
UPDATE, MERGE, or DELETE portion is made independently of the query portion, and
vice versa. Degree of Parallelism The DOP is determined by the same rules as for the queries. Note that in the case of UPDATE and DELETE operations, only the target table to be modified (the only reference object) is involved. Thus, the UPDATE or DELETE parallel hint specification takes precedence over the parallel declaration specification of the target table. In other words, the precedence order is: MERGE, UPDATE, DELETE hint > Session > Parallel declaration specification of target table.

A parallel execution server can update or merge into, or delete from multiple
partitions, but each partition can only be updated or deleted by one parallel execution server.

If the DOP is less than the number of partitions, then the first process to finish work on one partition continues working on another partition, and so on until the work is finished on all partitions. If the DOP is greater than the number of partitions involved in the operation, then the excess parallel execution servers will have no work to do.

Posted in: Parallel Execution