RE: Massive Update commit every 1000 records
- From: "Balakrishnan, Muru" <Muru.Balakrishnan@xxxxxxxxxxxxxxx>
- To: <yuldashev@xxxxxxxxxxx>, <wellmetus@xxxxxxxxx>
- Date: Fri, 13 Nov 2009 13:23:21 -0700
We have had good results with CTAS. Create a new table (no logging) with
only 4 digits and swap.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alisher Yuldashev
Sent: Friday, November 13, 2009 1:17 PM
To: wellmetus@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Massive Update commit every 1000 records
Roger,
I would do
declare
cursor c12 is select rowid rid from sales where ssn is not null;
type v_typ is table of c12%rowtype;
v_arr v_typ;
begin
open c12;
loop
fetch c12 bulk collect into v_arr limit 1000;
forall i in 1..v_arr.count
update sales set ssn=to_number(substr(to_char(ssn),-4)) where
rowid=c12.rid(i);
commit;
exit when c12%notfound;
end loop;
close c12;
end;
Thanks,
--
Alisher Yuldashev
Senior Oracle DBA
The Pythian Group - Ottawa, Canada
Web : http://www.pythian.com
> *Hi List,*
> **
> *Background:* 9i (9.2.0.8.0) HP-UX B.11.31 U ia64
> * *
> *We need to shrink SSN to the last 4 digit.*
> * *
> _update sales set ssn=to_number(substr(to_char(ssn),-4));_
>
> *But the sales table is quite large and we want to commit every 1000
> records.*
> * *
> *Does anyone have a better idea to do this rather than below?*
> * *
> *Thanks,*
> * *
> *Roger*
>
>
> declare
> cursor c12 is select ssn from sales for update of ssn;
> n number := 0;
> begin
> for eachc12 in c12 loop
> if eachc12.ssn is not null then
> n := n + 1;
> update sales set ssn=to_number(substr(to_char(ssn),-4)) where
> current of c12;
> if ( n = 1000 )
> then
> commit;
> end if;
> end if;
> end loop;
> commit;
> end;
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
