Home » SQL & PL/SQL » SQL & PL/SQL » BULK UPDATE
BULK UPDATE [message #333904] Mon, 14 July 2008 17:08 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Hi All

I want to update a table with 30 million rows. I want to do soething like this:

update table1
set col1 = trim(' ' from col1)

i am trying to use bulk this is where i have reached so far:
create procedure as

CURSOR cur_1 IS
   SELECT ROWID	FROM tab1 where substr(upc_id, 1, 1) not in ('0', '1', '2', '3', '4','5','6','7','8','9');
		      
type  array_inv_data is table of cur_1%ROWTYPE;
cur_array 	 array_inv_data;  

begin--main

OPEN cur_1;   			
LOOP --cursor
FETCH cur_inv_data BULK COLLECT INTO cur_array LIMIT 1000000;
EXIT WHEN cur_inv_data%NOTFOUND;
 begin
   update table1
      set col1 = trim(' ' from col1)
    where rowid = v_row_id;
   commit work; 					
   number_fixed := number_fixed + 1;	   
 exception
     when others then			
     number_not_fixed := number_not_fixed + 1;			 
     rollback;
 end;		
end loop; --end cursor loop
close cur_1;

end--main

but i dnt know if have to change my update statement or not coz still taking allot of time...

Thanks all

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Tue, 15 July 2008 00:21] by Moderator

Report message to a moderator

Re: BULK UPDATE [message #333905 is a reply to message #333904] Mon, 14 July 2008 17:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Re: BULK UPDATE [message #333921 is a reply to message #333905] Mon, 14 July 2008 21:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
See if this link helps.

Ross Leishman
Re: BULK UPDATE [message #333946 is a reply to message #333904] Tue, 15 July 2008 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You broke many rules:
- you didn't format
- you didn't copy and paste your session
- you didn't post a code that is syntaxically correct
- you didn't search before posting
- you use IM speak
...

Regards
Michel

[Updated on: Tue, 15 July 2008 00:19]

Report message to a moderator

Re: BULK UPDATE [message #333989 is a reply to message #333904] Tue, 15 July 2008 02:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're updating 30,000,000 rows - it's going to take a little while, particularly if you've got any triggers on that table that fire for every row level update.

YOur solution is going to be slower than doing a single update statement, although it may allow you to work round problems with an undersized rollback/undo tablespace.

It took me 12 seconds to update 100,000 rows using a single update statement (albeit on a database on my laptop - your server should be meatier than that).

Based on that, it would take me about 60 minutes to update 30,000,000 rows.

How long is it taking you (that's one of many pieces of information that you didn't provide us with)

[Updated on: Tue, 15 July 2008 02:33]

Report message to a moderator

Previous Topic: order of executing procedures
Next Topic: To delete a table from another table which has duplicate values in it
Goto Forum:
  


Current Time: Sat Dec 03 09:52:45 CST 2016

Total time taken to generate the page: 0.07200 seconds