RE: Massive Update commit every 1000 records

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;
>  

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: