Oracle Data Warehouse Tuning for 10g

336

12.2

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.



The CREATE INDEX and ALTER INDEX REBUILD commands

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: