This is largely due to the fact that most correlated updates require a full table scan. This results in very slow performance when the table is extremely large.
The following update statement is typical of correlated updates:
Update Target_Table
Set Target_Field = (Select Source_Information
From Source_Table
Where Source_Table.Key =
Target_Table.Key)
Where exists (Select 'x'
From Source_Table
Where Source_Table.Key =
Target_Table.Key)
Performance problems arise because there is no method of eliminating rows in the Target_Table based on information in the Source_Table. If other conditions in the Update's Where clause do not disqualify most of the rows in the Target_Table, this update will require substantial processing time.
The following PL/SQL code effectively uses an index on the Key field to access only the appropriate records in the Target_Table:
Declare
Cursor Source is
Select *
From Source_Table;
Begin
For Row in Source Loop
Update Target_Table
Set Target_Field = Row.Source_Information
Where Key = Row.Key;
End Loop;
Exception
When OTHERS Then
Null;
End;
This PL/SQL script loops through each of the records in the Source_Table and updates the appropriate row in the Target_Table, if any. Essentially, this transfers the full table scan to the Source_Table and allows the index on the Target_Table to be used effectively.
Running a typical correlated update on an 8,000 row table
to update 9 records required 19.4 CPU seconds. The same update
using the PL/SQL script executed in 1.12 CPU seconds
-- a 94% performance improvement.
The PL/SQL script will outperform the
correlated update whenever the Source_Table is smaller
than the Target_Table.
The larger the Target_Table compared to the Source_Table,
the more substantial the performance gain. With an effective
index, the size of the Target_Table is no longer a factor
in the time required to update the table; the number of records
being updated determines the performance.
Replacing the EXISTS subquery with IN subquery will give same improvement in most cases.
