Faster Updating??

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

Do you have an index on C.gen_insttu_code ?
Do you have an index on T.cust_no ?
(Actually a index on T(ipt_gen_insttu_code, cust_no) would probably be better)
Have you run ANALYZE TABLE xxx COMPUTE STATISTICS on these tables ?
Do you really need that DISTINCT ?
What does the "explain plan" say ?

Index on CUST_CONT C:
> CUST_CONT_IDX_1 (gen_insttu_code, cust_no, cust_seq_no)

Index on TEMP_UT_AC_CONV T:
> TEMP_UT_AC_CONV_IDX_2 (ipt_gen_insttu_code, ipt_user_id, ipt_session_id, ipt_timestamp, cust_no, err_no, err_f)

Here's how the explain plan look like:
- UPDATE STATEMENT, GOAL = CHOOSE
- UPDATE [CUST_CONT]
- FILTER
- INDEX RANGE SCAN [CUST_CONT_IDX_1]
- INDEX RANGE SCAN [TEMP_UT_AC_CONV_IDX_2]
- SORT UNIQUE
- TABLE ACCESS BY INDEX ROWID [TEMP_UT_AC_CONV]
- INDEX RANGE SCAN [TEMP_UT_AC_CONV_IDX_2]

I need the DISTINCT as in the TEMP_UT_AC_CONV table, records are unique on IPT_GEN_INSTTU_CODE, ACA_AC_NO, CUST_NO. So a CUST_NO will repeat itself on different ACA_AC_NO.

Index on TEMP_UT_AC_CONV T:
> TEMP_UT_AC_CONV_IDX_2 (ipt_gen_insttu_code, ipt_user_id, ipt_session_id, ipt_timestamp, cust_no, err_no, err_f)

The above index may not be good because you are joining the first column (ipt_gen_insttu_code) and 5th column (cust_no) to main table.

Create a new index on TEMP_UT_AC_CONV (ipt_gen_insttu_code,cust_no) and then analyze the table.

Tamilselvan

Oh, it really helps! 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