Bulk update for every 10k rown among 2L
Hi , Have a table in which 2lakh rows are present.I need to update a column and for every 10K i need a commit. Plese help me to write a script for this
Software/Hardware used:
Software/Hardware used:
ASKED:
October 16, 2009 1:43 PM
UPDATED:
October 22, 2009 2:24 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
You really didn’t give much info in your question. Here is a short generic discussion.
If you write one update statement all rows will be updated before the commit.
Using Oracle PL/SQL or SQL/PLUS or another language you could use a cursor to identify the rows you want to update, and then issue an update for each row in the cursor, count them, at 10,000 issue a commit, and return to the update process.
Using one update statement will cause lots of locks, but should run faster.
Using the cursor will be much slower, but should provide better concurrency.
More info is definitely necessary. even if you decide to commit every 10K, there are a few ways to do it depending on your situation.
A great article about this can be found on AskTom:
http://www.oracle.com/oramag/oracle/01-jul/o41asktom.html#
I find the following works best for me most of the time:
declare
v_count := 0;
cursor (….)
begin
for r in cursor
loop
v_count:= v_count + 1;
update rec;
if ( v_count= 1000 )
then
commit;
v_count:= 0;
end if;
end loop;
end;