Degree of Parallelism (Syntax)
12.1.1 What Can Be Executed in Parallel?
In an ideal world parallel queries are best executed on multiple CPU plat-
forms when Oracle Partitioning [1] is being used with separate disks or
RAID arrays. Generally, parallel queries are only an advantage for very large
tables or in very large databases, such as data warehouses. Using parallel
queries on small, highly active concurrent OLTP databases can cause, rather
than solve, performance problems. Certain types of Oracle SQL can be exe-
cuted in parallel:
Any query with at least a single full table scan using SELECT,
INSERT, UPDATE, and DELETE commands can be executed in
parallel. Large single table scans, large joins, and partitioned index
reads fall into this category. A partitioned index read implies access to
local partitioned indexes, where a local index is an index created on
each separate partition.
Fast full index scans can also be executed in parallel, as can full table
scans. This is because fast full index scans involve direct I/O activity.
This is shown in Figure 12.7. Some specific sections within queries
can be executed in parallel.
can be executed in parallel.
Using the CREATE TABLE statement generated from a SELECT
statement and the CREATE MATERIALIZED VIEW statement can
be executed in parallel.
Any type of mass DML operation or similar, involving INSERT,
UPDATE, DELETE, and MERGE statements can be executed in
parallel. This area includes not only DML operations, but also
SQL*Loader parallel and parallel-direct appends.
Moving and splitting of partitions can also be executed in parallel
using the MOVE and SPLIT partition clauses.
12.2 Degree of Parallelism (Syntax)
Parallelism can be executed by the optimizer in one of two ways:

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.