Parallel management tasks

7.8 Parallel management tasks

Throughout this book, the typically large size of the data warehouse means that tasks can take a very long time to complete. So far, we have considered some techniques for improving performance, but there is a very useful one that is worth mentioning again: the ability to run operations in parallel.

Normally, a single process executes a SQL statement, but imagine the performance gains we could achieve if we could split that SQL statement into a number of processes running alongside one another. Provided you are using Oracle 9i Enterprise edition, this capability is available to you.

Parallel execution is most useful for operations that access significant amounts of data, including queries, index creation, bulk inserts, updates, deletes, aggregations, and data movement. With every release, Oracle increases the number of operations and commands that can be run in parallel. At the time of writing most operations can be run in parallel, including:

• Parallel query—queries and subqueries in SELECT statements.

• Parallel DDL—including:

  • CREATE TABLE AS SELECT, CREATE INDEX, and ALTER INDEX REBUILD

  • For partitioned tables: ALTER TABLE MOVE, SPLIT, COALESCE

  • For partitioned indexes: ALTER INDEX REBUILD or SPLIT

• Parallel DML—INSERT, UPDATE, DELETE, multitable insert

• SQL*Loader and external tables

Parallel operations require accurate statistics to perform optimally. Chapter 3 discussed the use of parallelism to improve query performance, and Chapter 5 showed examples of loading data in parallel. Similar techniques are used to execute DDL statements in parallel.