Home » SQL & PL/SQL » SQL & PL/SQL » Huge Update
Huge Update [message #183365] Thu, 20 July 2006 09:40 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member

Oracle 9i Rel 2 Version.

Instead of giving a single update statement.

update document set doc_flag=1 where doc_flag is null;


so as not to run out of RBS, i am giving this

declare
 j number;
 begin
 select count(*) into j from document;
 for i in 1..j
 loop
 update document set doc_flag=1 where doc_flag is null and rownum <=200000;
 commit;
 end loop;
 end;

this will update 40 million rows, So like this it will update 200000 rows at a time, Is this code and approach correct.


Any suggestions on this code becuase i don't have data in Developement to test this number of rows in Production this is going to update around 40(+) million rows,
Basically this is a new column so as to add not null constraint and a default value i have to update this column.

Thanks
Re: Huge Update [message #183368 is a reply to message #183365] Thu, 20 July 2006 09:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best approach to this is you original one.

Slap your DBA until he increases the amount of rollback available, and then just do the update. 40 million rows isn't a large table, and you're only updating a single column.

If this isn't an option, a slightly quicker approach than yours would be

declare
 begin
 loop
 update document set doc_flag=1 where doc_flag is null and rownum <=200000;
 commit;
 exist when sql%rowcount=0;
 end loop;
 end;

There's no need to know how many records need to be updated - this will just keep going until it tries to update some rows and finds there aren't any.

But seriously - the quickest approach is to just do a single update.
Re: Huge Update [message #183372 is a reply to message #183368] Thu, 20 July 2006 10:14 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for you Prompt Response

If i am wrong can you please correct
exist when sql%rowcount=0;


It should be EXIT or Exist.



Thanks

Re: Huge Update [message #183375 is a reply to message #183372] Thu, 20 July 2006 10:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, perhaps you'd want to read this recent discussion about the commiting problem.
Re: Huge Update [message #183388 is a reply to message #183368] Thu, 20 July 2006 12:39 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks JRowbottom

I tried this one, but it is updating only 200000 rows and coming out of loop, Remaining rows are still null,
declare
 begin
 loop
 update document set doc_flag=1 where doc_flag is null and rownum <=200000;
 commit;
 exit when sql%rowcount=0;
 end loop;
 end;


Do i need to keep commit after end loop .


Thanks

[Updated on: Thu, 20 July 2006 12:58]

Report message to a moderator

Re: Huge Update [message #183418 is a reply to message #183375] Thu, 20 July 2006 18:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
begin
  loop
    update document
       set doc_flag = 1 
     where doc_flag is null 
       and rownum <= 200000;
    exit when sql%rowcount = 0;  -- this line needs to be before the commit
    commit;
  end loop;
  commit;
end;
Re: Huge Update [message #183962 is a reply to message #183418] Mon, 24 July 2006 09:55 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Todd
I tried your solution it is taking double the time it took with this one which i tried.


set autocommit on
set feedback on
set autocommit 200000
update  /*+ append parallel (a,12) */ xxx a set a.yyy = 1;



Any how will still look for other options, Thanks for Support.


Regards
Re: Huge Update [message #183969 is a reply to message #183962] Mon, 24 July 2006 10:25 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Well, a single statement will be faster, I'm not sure what your point is here. Your original question said you didn't have enough RBS to run a single statement, so which is it?
Re: Huge Update [message #183971 is a reply to message #183969] Mon, 24 July 2006 10:49 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Well Sorry if i confused you here, well my point just to avoid running out of RBS, i was looking for options other than single update statement.

Thanks
Re: Huge Update [message #184003 is a reply to message #183971] Mon, 24 July 2006 15:14 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Then you need to look at the code I provided again. You cannot place a constraint on the answer ("I can't do this in one statement without rollback space errors") and then complain about the alternative taking longer.
Re: Huge Update [message #184050 is a reply to message #184003] Tue, 25 July 2006 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Especially as I pointed out in my original answer that this solution was going to be slower than the single update approach.

Re: Huge Update [message #184065 is a reply to message #183971] Tue, 25 July 2006 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suppose you could try doing

CREATE TABLE temp_document AS
SELECT <columns>,decode(doc_flag,null,1,doc_flag)
FROM   document;

DROP TABLE document;

RENAME temp_document TO document;

and then recreate any indexes etc. You'd need to drop the constraints as well.

I'm assuming here that DDL commands that can't be rolled back don't generate Undo. DOes anyone out there know if I@m right in that arrumption?
If I'm not, then ignore this post.
Re: Huge Update [message #184248 is a reply to message #184065] Tue, 25 July 2006 21:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Correct. The only limitations to a CTAS are:
- Space to create the table
- PGA area to resolve sorts and hashes if the SQL needs them.

Ross Leishman
Previous Topic: not exists
Next Topic: Parsing XML
Goto Forum:
  


Current Time: Sat Dec 10 22:39:54 CST 2016

Total time taken to generate the page: 0.14368 seconds