Oracle Parallel Processing

Parallel DDL

Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned. For nonpartitioned tables and indexes, only the following types of DDL statements can be parallelized:

CREATE TABLE...AS SELECT
CREATE INDEX
ALTER INDEX...REBUILD

If you’re working with partitioned tables and indexes, the scope of Oracle’s parallel DDL support broadens. The following statements can be parallelized for partitioned tables and indexes:

CREATE TABLE...AS SELECT
ALTER TABLE...MOVE PARTITION
ALTER TABLE...SPLIT PARTITION
CREATE INDEX
ALTER INDEX...REBUILD PARTITION
ALTER INDEX...SPLIT PARTITION

Not all tables allow these operations to be executed in parallel. Tables with object columns or LOB columns don’t allow parallel DDL.

Setting the Degree of Parallelism

With DDL statements, you do not specify the degree of parallelism using hints. Instead, you use the PARALLEL clause. The PARALLEL clause takes the following form:

{NOPARALLEL |
PARALLEL (DEGREE {degree | DEFAULT} 
   [INSTANCES {instances | DEFAULT}] )}

Here’s an example that shows the PARALLEL clause being used in a CREATE INDEX statement. The specified DEGREE is 4, so 4 parallel slave processes will be used to create the index:

CREATE INDEX customer_ix ON customers (customer_id)
   TABLESPACE ind01
   STORAGE (INITIAL 100M NEXT 100M PCTINCREASE 0 MAXEXTENTS 20)
   PARALLEL (DEGREE 4);

Storage Issues for Parallel DDL

When you create a table or index using parallel DDL, two or more parallel slave processes work on behalf of your statement ...