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%.
DDL operations can be paralleled
if a parallel clause or declaration is specified in the syntax.
The parallel DDL statements for non-partitioned tables and indexes
CREATE TABLE ... AS select
ALTER INDEX ... REBUILD
The parallel DDL statements for
partitioned tables and indexes are:
CREATE TABLE ... AS select
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER TABLE ... COALESCE PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
The ALTER SESSION FORCE PARALLEL
DDL statement can be used to override the parallel clauses of
subsequent DDL statements in a session. The degree of parallelism is determined by the
specification in the parallel clause, unless an ALTER SESSION FORCE
PARALLEL DDL statement overrides it. A rebuild of a partitioned
index is never paralleled.
When indexes and tables are
created in parallel, each parallel execution server allocates a new
extent and fills the extent with the table or index data. Hence, if
an index is created with a degree of parallelism of four, the index will have at least
four extents initially. The parallel allocation of extents is the
same for indexes are built in parallel or partitions are being
moved, split, or rebuilt in parallel.
While serial operations only
require that the schema object has at least one extent, parallel
creations initially require that tables or indexes have at least as
many extents as there are parallel execution servers creating the
schema object. After creation, the extents may be coalesced, but
there must be a plan for the additional space that may be required
for the initial multiple extents caused by parallel DDL operations.
Rules for Paralleling Create
Table as Select (CTAS)
CTAS is a very widely used and
useful operation while dealing with the reorganization of large
tables. The CTAS statement contains two parts: a CREATE part (DDL)
and a SELECT part (query). Oracle can parallellize both parts of the
statement. The CREATE part of the CTAS must follow the same rules as
apply to other DDL operations. The following conditions must
be satisfied if the query part of a CTAS statement is to be
The query includes a parallel
hint specification, the CREATE part of the statement has a parallel
clause, or objects referred to in the query have a parallel
At least one of the tables
specified in the query requires a full table scan or an index range
scan spanning multiple partitions.
The degree of parallelism for the query part of
CTAS is then determined as follows:
The query part of the CTAS
uses the settings specified in the parallel clause of the CREATE
When the parallel clause is
not specified, the default degree of parallelism will be the number of CPUs.
When the CREATE is done as a
serial operation, the degree of parallelism is determined by the query settings.
The CREATE operation of CTAS can
be paralleled only by a parallel clause or an ALTER SESSION FORCE
PARALLEL DDL statement.
The degree of parallelism for the CREATE operation, and for
the SELECT operation if it is paralleled, is specified by the
parallel clause of the CREATE statement, unless it is overridden by
an ALTER SESSION FORCE PARALLEL DDL statement.
SQL*LOADER: Parallel Load
Placing large data loads into
database tables often involves considerable time. However, with the
help of concurrent or parallel loading, the whole loading process
can be accomplished faster.