Faster Updating?

[kclow]

Hi,

Just faced a problem on my update query, as it takes hours to execute. Anyone can help me to "improve" the attached stmt below ?

Update CUST_CONT C set (C.cust_addr_type_code, C.cust_fax_no, C.cust_email_txt, C.cust_person_txt, C.cust_phone_no, C.cust_mobile_phone_no, C.cust_pager_no, C.cust_contrmk_txt, C.cust_defmail_f)
= (Select DISTINCT T.GEN_ADDR_TYPE_CODE, T.ACA_FAX_NO, T.ACA_EMAIL_TXT, T.ACA_PERSON_TXT, T.ACA_PHONE_NO,
T.ACA_MOBILE_PHONE_NO, T.ACA_PAGER_NO, T.ACA_CONTRMK_TXT, 'Y'
From temp_ut_ac_conv T Where T.ipt_gen_insttu_code = 'XXX' and T.cust_no = C.cust_no)
Where C.gen_insttu_code = 'XXX'
and exists (Select * From temp_ut_ac_conv T
Where T.ipt_gen_insttu_code = 'XXX' and T.cust_no = C.cust_no);


P/S: The data in the table exceed 100,000 records.

Thanks....

kclow

 

[jrbarnett]

Which fields are keyed and/or indexed?

 

[kclow]

After I created a new index on TEMP_UT_AC_CONV T:
TEMP_UT_AC_CONV_IDX_2 (ipt_gen_insttu_code, cust_no), the update completes within few minutes!
However, just out of curiosity, why selected columns indexing run faster although both are under INDEX RANGE SCAN?


Also, at the same time, I have created a workaround using temp table before having the new index:

Insert into TEMP_CUST_ACA_CONT(cust_no, cust_addr_type_code, cust_fax_no, cust_email_txt, cust_person_txt, cust_phone_no, cust_mobile_phone_no, cust_pager_no, cust_contrmk_txt, cust_defmail_f) Select DISTINCT CUST_NO, GEN_ADDR_TYPE_CODE, ACA_FAX_NO, ACA_EMAIL_TXT, ACA_PERSON_TXT, ACA_PHONE_NO, ACA_MOBILE_PHONE_NO, ACA_PAGER_NO, ACA_CONTRMK_TXT, 'Y' From temp_ut_ac_conv Where ipt_gen_insttu_code = 'XXX' and exists (Select * From CUST_CONT Where gen_insttu_code = 'XXX' and cust_no = TEMP_UT_AC_CONV.CUST_NO);

// Update customer's account information in CUST_CONT
Update CUST_CONT C set (C.cust_addr_type_code, C.cust_fax_no, C.cust_email_txt, C.cust_person_txt, C.cust_phone_no, C.cust_mobile_phone_no, C.cust_pager_no, C.cust_contrmk_txt, C.cust_defmail_f) = (Select T.cust_addr_type_code, T.cust_fax_no, T.cust_email_txt, T.cust_person_txt, T.cust_phone_no, T.cust_mobile_phone_no, T.cust_pager_no, T.cust_contrmk_txt, 'Y' From temp_cust_aca_cont T Where T.cust_no = C.cust_no) Where C.gen_insttu_code = 'XXX' and exists (Select * From TEMP_CUST_ACA_CONT T Where T.cust_no = C.cust_no)";

Do you guys think this is preferable or the first updating stmt I have come out with (since the indexing helps)?

Thanks...

kclow

 

[jrbarnett]

I don't have any preference. As long as the job gets done, that's all that matters. If by adding an index and using a temporary table you reduced the runtime from hours to minutes, so much the better.

John