Re: Massive Update commit every 1000 records

From: Alisher Yuldashev <yuldashev_at_pythian.com>
Date: Fri, 13 Nov 2009 15:17:26 -0500
Message-ID: <4AFDBED6.70300_at_pythian.com>



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
Received on Fri Nov 13 2009 - 14:17:26 CST

Original text of this message