Oracle Parallel Processing

Parallel DML

Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it’s necessary to maintain large summary or historical tables. In OLTP systems, parallel DML sometimes can be used to improve the performance of long-running batch jobs.

Deciding to Parallelize a DML Statement

When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. For UPDATE and DELETE statements, the rules are identical. INSERT statements, however, have their own set of rules.

Tip

To parallelize DML (INSERT, UPDATE, and DELETE) statements, you must enable parallel DML. Refer to the section later in this chapter titled “Enabling Parallel DML” for details.

UPDATE and DELETE statements

Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved. You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition. If such a statement includes a subquery, then the decision to parallelize the UPDATE or DELETE operation is made independently of the decision to parallelize the subquery.

UPDATE or DELETE operations are parallelized only if the table being updated or deleted has a parallel specification or if a PARALLEL hint is ...