update query issue

[rk68]

Hi,

I am updating transaction file (around 7 lacs records) with member id from the member master table (has around 20K records).
To update the member id, using a key which is combination of code & member name.
Though the matching records in the transaction table is only about 15000, it takes about 5-6 hours to update.
I have the index key in place.
The query used is given below, I have tried using 2 different ways of updating the data but have same issue.
One is thru the loop & another is direct update. Can someone please let me what & where is the issue & why its taking time for update.

The SQL is given below:
-----------------
# 1:
update t_samrat2_trn x
set x.samrat2_trn_dlr_cd = (select h.samrat_mrg_parent_cd
from t_samrat_mbr_merge h
where upper(x.samrat2_trn_key) = upper(h.samrat_mrg_key)
and upper(h.samrat_mrg_f_a) = 'Y'
and (h.samrat_mrg_year * 100 + h.samrat_mrg_month) = (p_proc_yr * 100 + p_proc_mn)
and (x.samrat2_trn_pr_yr * 100 + x.samrat2_trn_pr_mn) = (p_proc_yr * 100 + p_proc_mn)
and upper(trim(x.samrat2_trn_region)) = upper(p_proc_rg)
and x.samrat2_trn_dlr_cd is null)
where exists (select h.samrat_mrg_parent_cd
from t_samrat_mbr_merge h
where upper(x.samrat2_trn_key) = upper(h.samrat_mrg_key)
and upper(h.samrat_mrg_f_a) = 'Y'
and (h.samrat_mrg_year * 100 + h.samrat_mrg_month) = (p_proc_yr * 100 + p_proc_mn)
and (x.samrat2_trn_pr_yr * 100 + x.samrat2_trn_pr_mn) = (p_proc_yr * 100 + p_proc_mn)
and upper(trim(x.samrat2_trn_region)) = upper(p_proc_rg)
and x.samrat2_trn_dlr_cd is null)
---------------
# 2

for cur_mbrup in (select t.samrat2_trn_key,
a.samrat_mrg_key,
a.samrat_mrg_parent_cd
from t_samrat2_trn t,
t_samrat_mbr_merge a
where upper(trim(t.samrat2_trn_region)) = upper(p_proc_rg)
and (t.samrat2_trn_pr_yr * 100 + t.samrat2_trn_pr_mn) = (p_proc_yr * 100 + p_proc_mn)
and upper(t.samrat2_trn_key) = upper(a.samrat_mrg_key)
and (a.samrat_mrg_year * 100 + a.samrat_mrg_month) = (p_proc_yr * 100 + p_proc_mn)
and upper(a.samrat_mrg_f_a) = 'Y'
and t.samrat2_trn_dlr_cd is null
order by t.samrat2_trn_key,
a.samrat_mrg_key,
a.samrat_mrg_parent_cd)

loop
update t_samrat2_trn x
set x.samrat2_trn_dlr_cd = cur_mbrup.samrat_mrg_parent_cd
where upper(trim(x.samrat2_trn_key)) = upper(trim(cur_mbrup.samrat_mrg_key))
and x.samrat2_trn_pr_yr * 100 + x.samrat2_trn_pr_mn = (p_proc_yr * 100 + p_proc_mn)
and upper(trim(x.samrat2_trn_region)) = upper(p_proc_rg) ;

commit;
end loop;

------------------------------



TIA,
Raj

 

[fredericofonseca]

what are the indexes (and respective columns) of all tables involved?
and what is the explain plan of both sqls

and with all those functions in place unless the index is a function index it probably isnt being used

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

and with all those functions in place unless the index is a function index it probably isnt being used

Frederico is correct, if you do not have "FUNCTION" indexes for those conditions, the explain plan will show "FULL TABLE SCAN's".
And . . . SQL #1 would definitely be faster.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ChrisHunt]

Maybe try something like this

MERGE INTO t_samrat2_trn t USING
   (select x.samrat2_trn_key
           h.samrat_mrg_parent_cd
    from   t_samrat2_trn x
           t_samrat_mbr_merge h
    where  upper(x.samrat2_trn_key) = upper(h.samrat_mrg_key)   -- do you really need the upper() here? 
    and upper(h.samrat_mrg_f_a) = 'Y' 
    and (h.samrat_mrg_year * 100 + h.samrat_mrg_month) = (p_proc_yr * 100 + p_proc_mn)
    and (x.samrat2_trn_pr_yr * 100 + x.samrat2_trn_pr_mn) = (p_proc_yr * 100 + p_proc_mn)
    and upper(trim(x.samrat2_trn_region)) = upper(p_proc_rg) 
    and x.samrat2_trn_dlr_cd is null) xx
ON (t.samrat_trn_key = xx.samrat_trn_key)
WHEN MATCHED THEN UPDATE SET t.samrat2_trn_dlr_cd = xx.samrat_mrg_parent_cd

That way, you're only running the query once. As noted, the chances are that those upper() functions are killing the chance of any indexes being used. I also don't like the look of this:

    and (h.samrat_mrg_year * 100 + h.samrat_mrg_month) = (p_proc_yr * 100 + p_proc_mn)

That looks like you're using number fields to store date information, and again stymies potential index use. This would be better:

    and h.samrat_mrg_year = p_proc_yr
    and samrat_mrg_month = p_proc_mn

Though it would be better still to use date columns to store date data!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd