Oracle7 Server SQL Reference

PARALLEL clause

Prerequisites

This clause can only be used in the following commands:

ALTER CLUSTER

ALTER DATABASE ... RECOVER

ALTER INDEX ... REBUILD

ALTER TABLE

CREATE CLUSTER

CREATE INDEX

CREATE TABLE

Syntax

Keywords and Parameters

NOPARALLEL

specifies serial execution of an operation. This is the default.

PARALLEL

specifies parallel execution of an operation.

DEGREE

determines the degree of parallelism for an operation on a single instance. That is, the number of query servers used in the parallel operation.

integer use integer query servers.

DEFAULT the number of query servers used is calculated from such things as the number of CPUs and the number of DEVICES storing tables to be scanned in parallel..

INSTANCES

determines the number of parallel server instances used in the parallel operation. This keyword is ignored if you do not have a parallel server.

integer use integer instances

DEFAULT use all available instances

Note: INSTANCES only applies to an instance using the Oracle7 Parallel Server.

Usage Notes

For more information on parallelized operations, see the "Parallel Query Option" chapter in Oracle7 Server Tuning.

Used in a CREATE command, the PARALLEL clause causes the creation of the object to be parallelized; if the CREATE command is CREATE TABLE, the PARALLEL clause sets the default degree of parallelism for queries on the table after creation.

Used in a command to alter an object, the PARALLEL clause changes the default degree of parallelism for queries on the object. In an ALTER DATABASE RECOVER command, the PARALLEL clause causes the recovery to be parallelized.

You cannot use the PARALLEL clause in an ALTER INDEX command unless you specify the REBUILD clause.

Specifying PARALLEL (DEGREE 1 INSTANCES 1) is equivalent to specifying NOPARALLEL.

A hint in a query can override a default of NOPARALLEL. Likewise, a hint in a query can override a default of PARALLEL.

CREATE SCHEMA

Although the PARALLEL clause syntax is allowed when creating a table, index or cluster in a CREATE SCHEMA statement, parallelism is not used and no error message is issued.

Example I

The following command creates a table using 10 query servers, 5 to scan scott.emp and another 5 to populate emp_dept:

CREATE TABLE emp_dept
	PARALLEL (DEGREE 5)
	AS SELECT * FROM scott.emp
			WHERE deptno = 10

Example II

The following command creates an index using 10 query servers, 5 to scan scott.emp and another 5 to populate the emp_idx index:

CREATE INDEX emp_idx
	ON scott.emp (ename)
	PARALLEL 5

Example III

The following command performs tablespace recovery using 5 recovery processes on 5 instances in a parallel server, for a total of 25 (5 * 5) query servers:

ALTER DATABASE
	RECOVER TABLESPACE binky
	PARALLEL (DEGREE 5 INSTANCES 5)

Example IV

The following command changes the default number of query servers used to query the EMP table:

ALTER TABLE emp
	PARALLEL (DEGREE 9)

Example V

The following command causes the index to be rebuilt from the existing index by using 6 query servers, 3 each to scan the old and to build the new index:

ALTER INDEX emp_idx
	REBUILD
	PARALLEL 3