Home » SQL & PL/SQL » SQL & PL/SQL » update statement (10.2.0.2)
update statement [message #384360] Tue, 03 February 2009 08:56 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
I am running the below code. it takes 3 hours to run. bp table has 400000 rows. ICP has 12 million rows.

declare
i integer;
begin
for i in ( select distinct item_sid from bp)
loop
UPDATE ICP b
SET BP_EFF_IRM = (Select base_eff_irm
                          From  BP a
                          Where a.item = b.item and
                                a.tree = b.tree and
                                a.cell = b.cell and
                                a.map_eff_irm = b.map_eff_irm and
                                b.mth between a.base_eff_irm and a.base_end_irm)
where b.item = i.item;
commit;
end loop;
end;  

Anu
Re: update statement [message #384361 is a reply to message #384360] Tue, 03 February 2009 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: update statement [message #384364 is a reply to message #384360] Tue, 03 February 2009 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
row by row is slow by slow

make sure index exists on
a.item = b.item and
a.tree = b.tree and
a.cell = b.cell and
a.map_eff_irm = b.map_eff_irm and
b.mth between a.base_eff_irm and a.base_end_irm)
b.item = i.item

COMMIT within loop will sooner or later generate ORA-01555 error

Make sure statistics are current.
Re: update statement [message #384367 is a reply to message #384360] Tue, 03 February 2009 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt that since the code you've posted won't actually compile - i.item definitely doesn't exist.
That said a couple of basic points.
1) i integer - huh?
If you're doing
FOR <something> IN <SELECT> LOOP 

that something is a record not an integer and you don't need to declare it.
2) Commiting inside loops is a really bad idea.
3) This would perform a lot better written as a single update statement, but for the amount of data you appear to be updating it's never going to be fast.
Re: update statement [message #384693 is a reply to message #384367] Wed, 04 February 2009 21:30 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use MERGE instead of UPDATE. See this article for the reason:
http://www.orafaq.com/node/1981

Ross Leishman
Previous Topic: How can I return a ref cursor from dbms_sql.
Next Topic: find special charecters in a column
Goto Forum:
  


Current Time: Fri Dec 09 23:13:14 CST 2016

Total time taken to generate the page: 0.07550 seconds