update with commits at certain counts

[mstrong]

Hello,

I think I have a simple question, but it's driving me crazy. I have to update a column in a table that has 97 million rows. The update is basically set status=Y where status=X. I'm trying to do a plsql block that will keep a counter, and commit after every 1000 records, but I can't get it right. I'm not sure if I need a cursor to do it or what, but it's not working. Anybody have a canned solution?

Mike

 

[ElenaS]

Try something like this:

declare
cursor upd_cur is
select rowid row_id
from some_table
where status = 'X';

counter integer := 0;

begin
for upd_rec in upd_cur loop
update some_table
set status = 'Y'
where rowid = upd_rec.row_id;

counter := counter + 1;
-- Commit every thousand records
if mod(counter, 1000) = 0 then
commit;
end if;
end loop;
-- Commit last batch of records
commit;
end;
/

Good luck

Elena

 

[carp]

A more efficient way would eliminate the counter:

declare
cursor upd_cur is
select rowid row_id
from some_table
where status = 'X';

begin
for upd_rec in upd_cur loop
update some_table
set status = 'Y'
where rowid = upd_rec.row_id;

-- Commit every thousand records
if mod(upd_cur%ROWCOUNT, 1000) = 0 then
commit;
end if;
end loop;
-- Commit last batch of records
commit;
end;
/

Elbert, CO
1321 MDT

 

[mstrong]

Thanks for the quick responses. I was testing the first one when I saw the second. I'll check them both out for speed now. Thanks again!

Mike

 

[jaggiebunnet]

A more efficient way would be to just update the table.

Why are you doing staged commits? Are you running out of Rollback? Then size the rollback accordingly.