Degree of Parallelism

The DOP is determined by the same rules as for the queries. For UPDATE and DELETE operations, only the target table to be modified (the only reference object) is involved. The UPDATE or DELETE parallel hint specification takes precedence over the parallel declaration specification of the target table. In other words, the precedence order is: MERGE, UPDATE, DELETE hint, then Session, and then Parallel declaration specification of target table. See Table 8-2 for precedence rules.

If the DOP is less than the number of partitions, then the first process to finish work on one partition continues working on another partition, and so on until the work is finished on all partitions. If the DOP is greater than the number of partitions involved in the operation, then the excess parallel execution servers have no work to do.

Example 8-4 illustrates an update operation that might be executed in parallel. If tbl_1 is a partitioned table and its table definition has a parallel clause and if the table has multiple partitions with c1 greater than 100, then the update operation is parallelized even if the scan on the table is serial (such as an index scan).

Example 8-4 Parallelization: Example 1

UPDATE tbl_1 SET c1=c1+1 WHERE c1>100;

Example 8-5 illustrates an update operation with a PARALLEL hint. Both the scan and update operations on tbl_2 are executed in parallel with degree four.

Example 8-5 Parallelization: Example 2

UPDATE /*+ PARALLEL(tbl_2,4) */ tbl_2 SET c1=c1+1;