Update or delete parallel hint specified at the statement...

Parallel hints are placed immediately after the UPDATE or DELETE keywords
in UPDATE and DELETE statements. The hint also applies to the underlying
scan of the table being changed.
Parallel clauses in CREATE TABLE and ALTER TABLE commands specify
table parallelism. If a parallel clause exists in a table definition, it determines the parallelism of DML statements as well as queries. If the DML statement
contains explicit parallel hints for a table, however, then those hints override the effect of parallel clauses for that table.
Decision to Parallelize
The following rule determines whether the update/delete operation should be
parallelized in an update/delete statement:
•
The UPDATE or DELETE operation will be parallelized if and only if the table being updated/deleted has a PARALLEL specification or the
PARALLEL hint is specified in the DML statement.
If the statement contains subqueries or updatable views, they may have their
own separate parallel hints or clauses, but these parallel directives do not
affect the decision to parallelize the update or delete.
Although the parallel hint or clause on the tables is used by both query and
update/delete portions to determine parallelism, the decision to parallelize
the update/delete portion is made independently of the query portion, and
vice versa.
Degree of Parallelism
The degree of parallelism is determined by the same rules as for the queries.
Note that in the case of update and delete operations, only one table (the only reference object) is involved which is the target table to be modified.
The precedence rule to determine the degree of parallelism for the update/
delete operation is that the update or delete parallel hint specification takes precedence over the parallel declaration specification of the target table:
Update/Delete hint > Parallel declaration specification of targe table
The maximum degree of parallelism you can achieve is equal to the number of
partitions in the table. A parallel server process can update into or delete from multiple partitions, but each partition can only be updated or deleted by one
parallel server process.
21-18 Oracle8 Server Concepts
If the degree of parallelism 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 degree of parallelism is greater than the number of partitions involved in the operation, then the excess parallel server processes would have no work to do.
Examples
UPDATE tbl_1 SET c1=c1+1 WHERE c1>100;
If TBL_1 is a partitioned table and its table definition has a parallel clause, then the update operation will be parallelized even if the scan on the table is serial (such as an index scan), assuming that the table has more than one partition
with C1 greater than 100.
UPDATE /*+ PARALLEL(tbl_2,4) */ tbl_2 SET c1=c1+1;
Both the scan and update operations on TBL_2 will be parallelized with
degree 4.
Rules for Parallelizing INSERT ... SELECT
An INSERT ... SELECT statement parallelizes its INSERT and SELECT
operations independently, except for the degree of parallelism.
You can specify a “parallel” hint after the INSERT keyword in an INSERT ...
SELECT statement. Since the tables being queried are usually not the same as
the table being inserted into, the hint allows you to specify parallel directives specifically for the insert operation.
You have four ways to specify parallel directives for an INSERT... SELECT
statement (assuming that PARALLEL DML mode is enabled):
1.
SELECT parallel hint(s) specified at the statement.
2.
Parallel clause(s) specified in the definition of tables being selected.
3.
INSERT parallel hint specified at the statement.
4.
Parallel clause specified in the definition of tables being inserted into.
Decision to Parallelize
The following rule determines whether the insert operation should be
parallelized in an INSERT... SELECT statement:
•
The INSERT operation will be parallelized if and only if the table being inserted into (the reference object) has a PARALLEL declaration
specification or the PARALLEL hint is specified after the INSERT in the
DML statement.
Parallel Execution 21-19
Hence the decision to parallelize the insert operation is made independently of the select operation, and vice versa.
Degree of Parallelism
Once the decision to parallelize the select and/or insert operation is made, one parallel directive is picked for deciding degree of parallelism of the whole statement using the following precedence rule:
Insert Hint directive > Parallel declaration specification of the inserting table > Maximum Query directive
where Maximum Query directive means that among multiple tables and
indexes, the table or index that has the maximum degree of parallelism
determines the parallelism for the query operation.
The chosen parallel directive is applied to both the select and insert operations.
Example
In the following example, the degree of parallelism used will be 2, which is the degree specified in the Insert hint: