One of the slowest commands in SQL is the UPDATE.Most often, when a
client complains about performance, a large, correlated update
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.
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 following PL/SQL code effectively uses an index on the Key field to access only the appropriate records in the Target_Table:
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.
Have a Oracle Question
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.