Oracle Parallel DML


The following DML statements can be parallelised.

Parallelising the SELECT Statement

The SELECT statement must include the PARALLEL hint or one object in the query was created including the PARALLEL declaration. Also the SELECT statement must be a full table scan or a multiple partition index range scan. Thus parallel SELECT statements are only applicable to partitioned tables and batch operations operating on whole tables. If no hint is present the PARALLEL clause is inherited from a parent object if it exists. The example below applies a degree of parallelism of 4.

SELECT /*+ PARALLEL(<tablename>, 4) */ count(*) FROM <tablename>;

Parallelising the UPDATE and DELETE Statements

UPDATE and DELETE statements can only be parallelised if their underlying objects are partitioned. Also parallelisation will only occur if the SQL statement has a hint or the table has PARALLEL in its specification. The maximum degree of parallelism is equivalent to the number of partitions and each transaction must be committed prior to the execution of a subsequent SQL statement.

DELETE /*+ PARALLEL(<tablename>, 4) */ FROM <tablename>

Parallelising the INSERT ... SELECT Statement

Partitioning is not neccessary. Multiple query servers are used to insert rows simultaneously.

INSERT /*+ PARALLEL(<tablename>, 4) */ INTO <tablename> SELECT * FROM <tablename>;

Direct Load Inserts

The direct parallel load insert is much like that of SQL*Loader. A direct load insert is where data is inserted into an object without using the database buffer cache. The data is inserted above the high-water mark of the object. After insertion is complete and committed the high-water mark is moved. Direct load inserts also do not do any logging but can be rolled back.