BULK UPDATE [message #333904] |
Mon, 14 July 2008 17:08  |
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 #333946 is a reply to message #333904] |
Tue, 15 July 2008 00:19   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
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
|
|
|