One of the slowest commands in SQL is the UPDATE.Most
often, when a client complains about performance, a large,
correlated update
(or,worse, an uncorrelated update) is at the root
of the problem. Often, even after creating the optimal indexes
and fine tuning the SQL
statement itself, the update is still hogging enormous
CPU resources and is still the bottleneck in the user's production scheme.
The Correlated Update
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.
In addition, this type of query suffers because an index would not help the Target_Table access time. In fact, the only index which will improve the performance of the preceding update is an index on the Source_Table Key field.
If the Source_Table is large, this is very useful.
Nevertheless, the best method of updating the table would be able to access just the correct rows in the Target_Table using an effective index. This method is now available using PL/SQL and an index on the Target_Table Key field.
The PL/SQL Update
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.
Performance Gain
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.
