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 to create the object. Each parallel slave process creates a temporary segment during the creation process. At the end, the parallel coordinator trims each of these temporary segments to release any free space and then combines these segments into one segment.

When you are using parallel DDL to create an object such as a table or an index, you need to be aware of two issues:

• How space is allocated

• The potential for fragmentation

You should be careful while specifying the storage clause for tables or indexes being created in parallel, because the amount of space allocated may be much greater than what you would otherwise expect. When you create a table or an index using parallel DDL, each parallel slave process allocates space based on the table or index’s storage clause. For example, if you create a table with INITIAL 10 MB and a degree of parallelism of 4, then each of the four parallel slave processes allocates an extent of 10 MB. The result is that a total of 40 MB will be allocated for the table during creation.

When you create a table or index using parallel DDL, it is possible to introduce fragmentation into the object during creation. If all the space in the temporary segments allocated by the parallel slave processes is not used, the unused space will be trimmed out of the temporary segments and returned to the database as free space. When the temporary segments are merged, that free space remains in between the extents, causing the table or index to be fragmented.