INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

update query issue

update query issue

(OP)
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

RE: update query issue

Quote (Federico)

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.
3eyes

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

RE: update query issue

Maybe try something like this

CODE

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:

CODE

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close