Using PL/SQL to Enhance Performance

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

See Also
Nested Conditions In SQL Query

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.