Update Statements and Correlated Updates

One  of  the slowest commands in SQL is the 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.

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. 

Also read:

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.