Monday, February 19, 2007

Parallel Query

Parallel Query

1. Introduced since Oracle 7.1 as Oracle Parallel Query Option (PQO) Parallel operations include:
* SELECT, FTS or index range scan spanning multiple partitions
* Creating or rebuilding an index
* Partition operations such as moving or splitting partitions
* CREATE TABLE AS SELECT
* INSERT INTO . . . SELECT
* Update and delete operations on partitioned tables

2. To parallelize a SELECT statement, the following conditions must be met:
* FTS or Index range scan involving multiple partitions.
* PARALLEL hint in FTS or PARALLEL on table definition.
* On an index PARALLEL_INDEX hint or index must have a parallel definition.

Example
alter table emp parallel (degree 4);
select degree from user_tables where table_name = 'EMP';
select count(*) from emp;
alter table emp noparallel;
SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp;

3. Parallel DML
* Only when multiple partitions are involved.
* Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.

Examples
alter session enable parallel dml;
insert /*+ parallel (emp_big,4,1) */ into emp_big select * from emp;
commit;
alter session disable parallel dml;

4. Parallel DDL
Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned.

CREATE TABLE...AS SELECT
CREATE INDEX
ALTER INDEX...REBUILD

Not all tables allow these operations to be executed in parallel.
Tables with object columns or LOB columns don't allow parallel DDL.

5. Parallel Data Loading::
SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE

6. Parallel Recovery
The RECOVERY_PARALLELISM initialization parameter controls
the degree of parallelism to use for a recovery.
You can override that setting for a specific situation
by using the RECOVER command's PARALLEL clause.

Example
RECOVER TABLESPACE tab PARALLEL (DEGREE 4);
RECOVER DATABASE PARALLEL (DEGREE DEFAULT);

A value of or 1 indicates serial recovery, no parallelism will be used.
The RECOVERY_PARALLELISM parameter setting cannot exceed
the PARALLEL_MAX_SERVERS setting.

No comments: