Database Reference
In-Depth Information
than solve performance problems. The following types of SQL can be exe-
cuted in parallel
1
:
Any query with at least a single table scan using SELECT, INSERT,
UPDATE, and DELETE commands.
The CREATE INDEX and ALTER INDEX REBUILD commands.
The CREATE TABLE command when generating a table from a
SELECT command.
Any query on partitions with local indexes, where a local index is an
index created on each separate partition.
There are two ways to execute queries against tables in parallel. The first
involves the PARALLEL hint and the second involves the CREATE
TABLE or ALTER TABLE commands including the parallel clause. For
instance, the PARALLEL hint can be used as follows to execute two parallel
processes, executing the query:
SELECT /*+ PARALLEL(SALES, 2) */ * FROM SALES;
The CREATE TABLE and ALTER TABLE commands can be used with
the following syntax:
{ CREATE | ALTER } TABLE … [ NOPARALLEL | PARALLEL [n] ];
Thus the SALES table could be altered to have a degree of parallelism of
2 with the following command:
ALTER TABLE SALES PARALLEL 2;
Note:
No parallel query examples are given in this topic, because parallel
queries tend to execute with improved performance only when using Ora-
cle Partitioning. Further detail and examples on Oracle Partitioning and
parallel queries can be found in my other topic, Oracle Performance Tuning
for 9
i
and 10
g
(ISBN 1-55558-305-9).
