Summary of Parallelization Rules

Table 8-2 shows how various types of SQL statements can be executed in parallel and indicates which methods of specifying parallelism take precedence: clause, hint, or underlying table or index declaration.

In Table 8-2, the following priority order applies:

  • The priority (1) specification overrides priority (2) and priority (3).

  • The priority (2) specification overrides priority (3).

Table 8-2 Parallelization Priority Order

Parallel Operation PARALLEL Hint PARALLEL Clause ALTER SESSION Parallel Declaration

Parallel query table scan (partitioned or nonpartitioned table)

(Priority 1) PARALLEL

  

(Priority 2) FORCE PARALLEL QUERY

(Priority 3) of table

Parallel query index range scan (partitioned index)

(Priority 1) PARALLEL_INDEX

  

(Priority 2) FORCE PARALLEL QUERY

(Priority 2) of index

Parallel UPDATE or DELETE (partitioned table only)

(Priority 1) PARALLEL

  

(Priority 2) FORCE PARALLEL DML

(Priority 3) of table being updated or deleted from

INSERT operation of parallel INSERT... SELECT (partitioned or nonpartitioned table)

(Priority 1) PARALLEL of insert

  

(Priority 2) FORCE PARALLEL DML

(Priority 3) of table being inserted into

SELECT operation of INSERT SELECT when INSERT is parallel

Takes degree from INSERT statement

Takes degree from INSERT statement

Takes degree from INSERT statement

Takes degree from INSERT statement

SELECT operation of INSERT SELECT when INSERT is serial

(Priority 1) PARALLEL

    

(Priority 2) of table being selected from

CREATE operation of parallel CREATE TABLE AS SELECT (partitioned or nonpartitioned table)

Note: Hint in the SELECT clause does not affect the CREATE operation

(Priority 2)

(Priority 1) FORCE PARALLEL DDL

  

SELECT operation of CREATE TABLE AS SELECT when CREATE is parallel

Takes degree from CREATE statement

Takes degree from CREATE statement

Takes degree from CREATE statement

Takes degree from CREATE statement

SELECT operation of CREATE TABLE AS SELECT when CREATE is serial

(Priority 1) PARALLEL or PARALLEL_INDEX

    

(Priority 2) of querying tables or partitioned indexes

Parallel CREATE INDEX (partitioned or nonpartitioned index)

  

(Priority 2)

(Priority 1) FORCE PARALLEL DDL

  

Parallel REBUILD INDEX (nonpartitioned index)

  

(Priority 2)

(Priority 1) FORCE PARALLEL DDL

  

REBUILD INDEX (partitioned index)—never parallelized

        

Parallel REBUILD INDEX partition

  

(Priority 2)

(Priority 1) FORCE PARALLEL DDL

  

Parallel MOVE or SPLIT partition

  

(Priority 2)

(Priority 1) FORCE PARALLEL DDL

  

See Also: