Document ID:	    10696.1
Subject:            UPDATE PERFORMANCE
Last Revision Date: 11 October   1994
Author: 	John R. Pack                                                   


                          Using PL/SQL to Enhance
                            Update Performance

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 preceeding 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.
ADDENDUM

Replacing the EXISTS subquery with IN subquery will give same improvem
ent
in most cases. See Bulletin 79213.039 for more information.