Parallel Server   «Prev  Next»

Lesson 3Parallel DML
ObjectiveRecognize correct syntax for parallel DML.

Parallel DML

In some instances, you may want to simultaneously insert, update, or delete data in the OPS system. Oracle8i enables you to do these tasks with the Data Manipulation Language (DML) parallel feature. You must study your individual applications and environment to determine whether using this new feature will optimize your system performance. The following is an example where your applications would benefit from using parallel DML. Let’s say that your parallel system has partitioned data along geographic lines to match the ten locations you have. When sales occur in each store, the data from the sales are updated in each store's partition. Your monthly sales account processing handles updates to this data on all the partitions. This means that your batch job’s updates could benefit from parallel DML, in which every partition’s, and thus each store's, updates are done concurrently.

Before you use parallel DML, Oracle recommends that you modify these initialization parameters on your OPS:
  1. Set PARALLEL_AUTOMATIC_TUNING to TRUE. This allows the optimizer to choose the degree of parallelism and other factors to speed up processing.
  2. Increase ENQUEUE_RESOURCES to allow for more space needed for parallel DML.
  3. Increase DML_LOCKS to allow for additional locks used during parallel DML.

In the following MouseOver, you will see syntax and examples of the parallel insert command, parallel update command, and parallel delete command.
  1. This command tells Oracle8<i>i</i> to consider all transactions following the command for parallel execution. Use this command, or use hints (see below) to enable parallel execution of specific commands.
  2. The table that is modified must be a partitioned table to use parallel execution. The syntax is identical to non-parallel DDL.
  3. In general, you should execute only one parallel execution command per transaction - issue a commit between transactions.
  4. A parallel UPDATE command uses many more locks than a non-parallel (serial) UPDATE command.
  5. This hint tells Oracle8<i>i</i> to use parallel execution of the command with a degree of parallelism set to five. There are many other hints you can use to customize parallel execution, but this is the main one.

Enable Parallel DML Here are some important restrictions that you should consider before using parallel DML:
  1. Replication does not support parallel DML.
  2. Triggers are not supported for parallel DML.
  3. Certain constraints are not allowed for parallel DML: self-referential constraints, delete cascade constraints, and deferred integrity.
  4. You cannot use parallel DML with object tables, tables with LOB data, index-organized tables, or clustered tables.
The next lesson covers parallel queries for object tables.