Types of Parallelism

The following types of parallelism are discussed in this post:
■ Parallel Query
■ Parallel DDL
■ Parallel DML
■ Parallel Execution of Functions
■ Other Types of Parallelism


Parallel Query
You can parallelize queries and subqueries in SELECT statements. You can also
parallelize the query portions of DDL statements and DML statements (INSERT,
UPDATE, and DELETE). You can also query external tables in parallel


Parallel Queries on Index-Organized Tables
The following parallel scan methods are supported on index-organized tables:
■ Parallel fast full scan of a nonpartitioned index-organized table
■ Parallel fast full scan of a partitioned index-organized table
■ Parallel index range scan of a partitioned index-organized table

These scan methods can be used for index-organized tables with overflow areas and
for index-organized tables that contain LOBs.

Nonpartitioned Index-Organized Tables
Parallel query on a nonpartitioned index-organized table uses parallel fast full scan. The DOP is determined, in decreasing order of priority, by:

1. A PARALLEL hint (if present)
2. An ALTER SESSION FORCE PARALLEL QUERY statement
3. The parallel degree associated with the table, if the parallel degree is specified in the CREATE TABLE or ALTER TABLE statement

The allocation of work is done by dividing the index segment into a sufficiently large number of block ranges and then assigning the block ranges to parallel execution servers in a demand-driven manner. The overflow blocks corresponding to any row are accessed in a demand-driven manner only by the process which owns that row.


Partitioned Index-Organized Tables
Both index range scan and fast full scan can be performed in parallel. For parallel fast full scan, parallelization is exactly the same as for nonpartitioned index-organized tables. For parallel index range scan on partitioned index-organized tables, the DOP is the minimum of the degree picked up from the previous priority list (like in parallel fast full scan) and the number of partitions in the index-organized table. Depending on the DOP, each parallel execution server gets one or more partitions (assigned in a demand-driven manner), each of which contains the primary key index segment and the associated overflow segment, if any.


Parallel Queries on Object Types
Parallel queries can be performed on object type tables and tables containing object
type columns. Parallel query for object types supports all of the features that are
available for sequential queries on object types, including:
■ Methods on object types
Attribute access of object types
■ Constructors to create object type instances
■ Object views
■ PL/SQL and OCI queries for object types
There are no limitations on the size of the object types for parallel queries.
The following restrictions apply to using parallel query for object types.
■ A MAP function is needed to parallelize queries involving joins and sorts (through
ORDER BY, GROUP BY, or set operations). In the absence of a MAP function, the
query will automatically be executed serially.
■ Parallel DML and parallel DDL are not supported with object types. DML and
DDL statements are always performed serially.

In all cases where the query cannot execute in parallel because of any of these
restrictions, the whole query executes serially without giving an error message.


Parallel DDL
This section includes the following topics on parallelism for DDL statements:
■ DDL Statements That Can Be Parallelized
■ CREATE TABLE ... AS SELECT in Parallel
■ Recoverability and Parallel DDL
■ Space Management for Parallel DDL

DDL Statements That Can Be Parallelized
You can parallelize DDL statements for tables and indexes that are nonpartitioned or
partitioned.

The parallel DDL statements for nonpartitioned tables and indexes are:
■ CREATE INDEX
■ CREATE TABLE ... AS SELECT
■ ALTER INDEX ... REBUILD
The parallel DDL statements for partitioned tables and indexes are:
■ CREATE INDEX
■ CREATE TABLE ... AS SELECT
■ ALTER TABLE ... [MOVE|SPLIT|COALESCE] PARTITION
■ ALTER INDEX ... [REBUILD|SPLIT] PARTITION
■ This statement can be executed in parallel only if the (global) index partition
being split is usable.

All of these DDL operations can be performed in no-logging mode for either parallel
or serial execution.

CREATE TABLE for an index-organized table can be parallelized either with or without
an AS SELECT clause.Different parallelism is used for different operations
Parallel CREATE TABLE ... AS SELECT statements on partitioned tables and parallel
CREATE INDEX statements on partitioned indexes execute with a DOP equal to the
number of partitions.
Partition parallel analyze table is made less necessary by the ANALYZE {TABLE,
INDEX} PARTITION statements, since parallel analyze of an entire partitioned table
can be constructed with multiple user sessions.
Parallel DDL cannot occur on tables with object columns. Parallel DDL cannot occur
on non-partitioned tables with LOB columns.


CREATE TABLE ... AS SELECT in Parallel
For performance reasons, decision support applications often require large amounts of
data to be summarized or rolled up into smaller tables for use with ad hoc, decision
support queries. Rollup occurs regularly (such as nightly or weekly) during a short
period of system inactively.
Parallel execution lets you parallelize the query and create operations of creating a
table as a subquery from another table or set of tables.

Clustered tables cannot be created and populated in parallel.

Parallel DML
Parallel DML (PARALLEL INSERT, UPDATE, DELETE, and MERGE) uses parallel
execution mechanisms to speed up or scale up large DML operations against large
database tables and indexes.


Other Types of Parallelism
In addition to parallel SQL execution, Oracle can use parallelism for the following
types of operations:
■ Parallel recovery
■ Parallel propagation (replication)
■ Parallel load (the SQL*Loader utility)

Like parallel SQL, parallel recovery and propagation are performed by a parallel
execution coordinator and multiple parallel execution servers. Parallel load, however, uses a different mechanism.

The behavior of the parallel execution coordinator and parallel execution servers may
differ, depending on what kind of operation they perform (SQL, recovery, or propagation). For example, if all parallel execution servers in the pool are occupied
and the maximum number of parallel execution servers has been started:
■ In parallel SQL, the parallel execution coordinator switches to serial processing.
■ In parallel propagation, the parallel execution coordinator returns an error.
For a given session, the parallel execution coordinator coordinates only one kind of
operation. A parallel execution coordinator cannot coordinate, for example, parallel
SQL and parallel recovery or propagation at the same time.

Posted in: Parallel Execution