|One of the slowest commands in SQL is the
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:
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:
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
The PL/SQL script will outperform the
correlated update whenever the Source_Table is smaller
than the Target_Table.
Replacing the EXISTS subquery with IN subquery will give same improvement in most cases.
Do you have an Oracle Question?
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.