This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
Queries and sub-queries can be
paralleled in SELECT statements. The query portions of DDL
statements and DML statements, such as INSERT, UPDATE and DELETE,
can also be paralleled. However, the query portion of a DDL or DML
statement cannot be paralleled if it references a remote object.
A SELECT statement can be
paralleled 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 a full table scan or an index range
scan spanning multiple partitions.
* parallel_index is effective
only on partitioned indexes and only when the range scans are being
* Also, it may be noted that
queries cannot be paralleled if there is a nested table involved.
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
the parallel declaration specification. Also, note that the parallel
hint is used only for operations on a table and the parallel_index
hint parallels an index range scan of a partitioned index.
As discussed in an earlier
section, the DOP can be specified within a table or index definition
by using one of the following statements: CREATE TABLE, ALTER TABLE,
CREATE INDEX or ALTER INDEX.
Parallel DML: UPDATE, MERGE,
UPDATEs, MERGEs and DELETEs can
only be paralleled on partitioned tables.
To specify parallel directives,
follow one of the following methods:
* ALTER SESSION FORCE PARALLEL
* Use an UPDATE, MERGE or DELETE
parallel hint in the statement.
* Use a parallel clause in the
definition of the table or reference object being updated or
The precedence rule in this
category of operations is: Hint --> Session --> Parallel declaration
specification of target table.
If the statement contains
sub-queries or updatable views, they may have their own separate
parallel hints or clauses. Remember, these parallel directives do
not affect decisions to parallelize the UPDATE, MERGE or DELETE
Once it is decided to use
parallel processing, the maximum DOP that can be achieved in DELETE,
MERGE and UPDATE is equal to the number of partitions or
sub-partitions, in the case of composite sub-partitions, in the
table. Multiple partitions can be updated, merged into or deleted
from by a single parallel execution, but each partition can only be
updated or deleted by one parallel execution server at a time. Each
parallel process transaction is a result of a different parallel
execution server. Therefore, parallel DML requires multiple undo
segments for performance.
However, there are some
restrictions, quoting the Oracle documentation for accuracy, as
* A transaction can contain
multiple parallel DML statements that modify different tables, but
after a parallel DML statement modifies a table, no subsequent
serial or parallel statement (DML or query) can access the same
table again in that transaction.
* Parallel DML operations cannot
be done on tables with triggers. Relevant triggers must be disabled
in order to parallel DML on the table.
* A transaction involved in a
parallel DML operation cannot be or become a distributed
* Clustered tables are not
Parallel DML: INSERT ?.
An INSERT ? SELECT statement
parallels its INSERT and SELECT operations independently, except for
the DOP. The INSERT operation will be paralleled if at least one of
the following is true:
* The parallel hint is used in
the INSERT in the DML statement.
* The table being inserted into
was specified with a parallel declaration specification.
* The session is under the
affect of an ALTER SESSION FORCE PARALLEL DML statement.
The select statement follows its
own set of rules, as specified in the query section. Only one
parallel directive is picked for deciding the DOP of the whole
statement. Then the chosen DOP is applied to both the SELECT and
INSERT operations using the precedence rule: Insert Hint directive
--> Session --> Parallel declaration specification of the inserting
table --> Maximum query directive.