I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect.

update updtbl a
set    updcol = 
(select rtnval 
 from   srctbl s
 where  s.qcol1=a.qcol1
 and    s.qcol2=a.qcol2
 and    s.qcol3=a.qcol3
 and    s.qcol4=a.qcol4);

All the columns referenced in srctbl, including rtnval, are columns in an index; qcol columns are the first ones in the index.

This update is currently sitting at four hours and counting. The database server is on a Linux box. I have a lot more updates such as this to perform on this database. What can I do to improve this performance?

    Requires Free Membership to View

Here are some suggestions.
  1. Is the updcol column on the updtbl table indexed? If it is, I would first drop or disable the index, and then run this update. After the update is complete, then rebuild the index.
  2. Have you run an Explain Plan on the entire update statement to verify it is using the index on the srctbl in the subquery? If not, please do so to see what access path the optimizer is using. If it is not using an index it is doing a table scan of the scrtbl table for each row in the updtbl table. Also, look at the subquery and run an explain plan on it as well. How long does the subquery take to run when run by itself? If it is using the index what type of access on the index is it using? If the subquery takes a long time to run, then start by tuning it first.
Hope this helps.

This was first published in June 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: