Home » SQL & PL/SQL » SQL & PL/SQL » update statement
update statement [message #243557] Thu, 07 June 2007 13:51 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

UPDATE NP N
SET (NP,NP_SOURCE,UPDATE_DATETIME)
      = (SELECT S.NP, S.NP_SOURCE,SYSDATE
         FROM TEMP_PRICE S
         WHERE S.ITEM_SID = N.ITEM_SID
         AND S.INDEX_SID = N.INDEX_SID
         AND S.TV_SID = N.TV_SID
         AND S.iRM = N.IRM)    
WHERE item_sid in ( select item_sid from temp_net_price);



the above update takes very long..

could any one tell me what's wrong with this..


Re: update statement [message #243560 is a reply to message #243557] Thu, 07 June 2007 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>could any one tell me what's wrong with this..
We can guess because you've posted minimal information.
OS Name & version?
Oracle version to 4 decimal places.
Post EXPLAIN_PLAN
Post DESC of all 3 tables involved.
Are statistics current; especially for "temp_net_price" table?
Why is "temp" table actually included?
How many rows in each table?

[Updated on: Thu, 07 June 2007 14:00] by Moderator

Report message to a moderator

Re: update statement [message #243709 is a reply to message #243557] Fri, 08 June 2007 10:21 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Do you have an index on item_sid in temp_net_price?
Re: update statement [message #243776 is a reply to message #243709] Fri, 08 June 2007 23:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is a nested update. Nested updates are very inefficient for updating a large number of rows. You should try an Updateable Join View.

Ross Leishman
Re: update statement [message #244120 is a reply to message #243557] Mon, 11 June 2007 11:40 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
UPDATE (SELECT N.NET_PRICE,N.NET_PRICE_SOURCE,UPDATE_DATETIME,
         S.NET_PRICE as NEW_NET_PRICE, S.NET_PRICE_SOURCE as   NEW_NET_PRICE_SOURCE,SYSDATE AS NEW_UPDATE_DATETIME
         FROM TEMP_NET_PRICE S, NET_PRICE N
         WHERE S.ITEM = N.ITEM
         AND S.INDEX = N.INDEX
         AND S.TREE = N.TREE
         AND S.IRM = N.IRM )    
SET NET_PRICE = NEW_NET_PRICE,
    NET_PRICE_SOURCE = NEW_NET_PRICE_SOURCE,    
    UPDATE_DATETIME = NEW_UPDATE_DATETIME;

I tried to do the above. both net_price & temp_net_price has unique key declared on (item,index, tree, irm). Still took about 10 minutes to update 500,000 rows..

Re: update statement [message #244190 is a reply to message #244120] Mon, 11 June 2007 22:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
1. Post the explain plan.
2. How many rows are in NP?

Ross Leishman
Re: update statement [message #244433 is a reply to message #243557] Tue, 12 June 2007 15:15 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
explain plan
UPDATE STATEMENT ()	[NULL]
 UPDATE ()	NET_PRICE
  HASH JOIN ()	[NULL]
   TABLE ACCESS (FULL)	TEMP_NET_PRICE_ANU
   TABLE ACCESS (FULL)	NET_PRICE


net_price has 5 million records. temp_net_price has about 560,000 records. both temp_net_price & net_price has unique key declared on all four columns (item,index,tree,irm).

Ananthi
Re: update statement [message #244438 is a reply to message #244433] Tue, 12 June 2007 15:47 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I've never seen an explain plan look like that, so I am just guessing...do you have up to date statistics, or any statistics at all?
Re: update statement [message #244439 is a reply to message #243557] Tue, 12 June 2007 16:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
1/2 million rows updated in 10 minutes. Why is that so bad?

Do you have some indication that it should take lots less?
Re: update statement [message #244451 is a reply to message #244439] Tue, 12 June 2007 22:12 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I agree with Kevin. The plan is as expected - no surprises. That's probably how long it takes to update 500K rows.

10mins is quite reasonable depending on hardware and configuration.

Ross Leishman
Previous Topic: bull collect loop syntax
Next Topic: Handling error in Multiple Procs.........
Goto Forum:
  


Current Time: Thu Dec 08 04:11:43 CST 2016

Total time taken to generate the page: 0.08268 seconds