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:
Set PARALLEL_AUTOMATIC_TUNING to TRUE. This allows the optimizer to choose the degree of parallelism and other factors to speed up processing.
Increase ENQUEUE_RESOURCES to allow for more space needed for parallel DML.
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.
Enable Parallel DML
Here are some important restrictions that you should consider before using parallel DML:
Replication does not support parallel DML.
Triggers are not supported for parallel DML.
Certain constraints are not allowed for parallel DML: self-referential constraints, delete cascade constraints, and deferred integrity.
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.