Re: Massive Update commit every 1000 records
- From: Roger Xu <wellmetus@xxxxxxxxx>
- To: Alisher Yuldashev <yuldashev@xxxxxxxxxxx>
- Date: Fri, 13 Nov 2009 14:47:01 -0600
First of all, thanks for everyone's reply and sorry for my mistake not
resetting counter back to zero after commit. We do have an index on SSN but
I want to drop it to speed up the update. It looks to me that a better way
is either CTAS or using bulk collect (below). Considering the standby
database in place, I think I will go with bulk collect to reduce the redo
transport. Thanks, Roger Xu
On Fri, Nov 13, 2009 at 2:17 PM, Alisher Yuldashev <yuldashev@xxxxxxxxxxx>wrote:
> 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
>
>
>
Other related posts:
