How to improve performance of column update

I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four hours and counting. What can I do to improve this performance?

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.

Please check the box if you want to proceed.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

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?

Here are some suggestions.

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.

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.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

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