Database Reference
In-Depth Information
•
When both parallel queries and DML statements are enabled, the update part (operation 3) is
executed in parallel. In this case, only one set of slave processes is used (operations 2 through
5 have the same value in the
TQ
column). This implies that each slave process scans the
granules of the table and modifies the rows as it finds them:
SQL> ALTER SESSION ENABLE PARALLEL QUERY;
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> ALTER TABLE t PARALLEL 2;
SQL> UPDATE t SET id = id + 1;
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | T | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
-----------------------------------------------------------------------
Parallel DDL Statements
Parallel DDL statements are supported for tables and indexes. The following are operations that are typically
parallelized:
•
CREATE TABLE ... AS SELECT ...
(CTAS) statements
Creation and rebuild of indexes
•
•
Creation and validation of constraints
In addition, partition-management operations such as
COALESCE
,
MOVE
, and
SPLIT
can be parallelized for
partitioned tables and indexes. Usually, DDL statements that can take advantage of parallel processing provide the
PARALLEL
clause (as you'll see shortly, constraints are an exception) to specify whether parallel processing should be
used and, if it is used, the degree of parallelism.
Parallel DDL statements are
enabled
by default. At the session level, you can enable and disable them with the
following SQL statements:
ALTER SESSION ENABLE PARALLEL DDL
ALTER SESSION DISABLE PARALLEL DDL
You can also force parallel executions with a specific degree of parallelism (for DDL statements that support it)
using the following SQL statement:
ALTER SESSION FORCE PARALLEL DDL PARALLEL 4
