Re: large deletions

From: Remco Blaakmeer <remco_at_rd31-144.quicknet.nl>
Date: 20 Feb 1999 21:51:35 GMT
Message-ID: <7anap7$ua9$1@rd31-144.quicknet.nl>

In article <36CD683E.4F8081FC_at_quasar.no>,

        Erik Ulven <erik_at_quasar.no> writes:
>
> Hello out there.
>
> I have a problem. We need to delete pretty much data from our
> application. The syntax is simple:
> i.e:
>
> delete from table1 where not_unique_id < 10000;
>
> The rollbac segments goes full, and i dont want to expand the rollback
> segments all the time.
> Is there a way to make oracle commit during this deletion, or another
> good solution? Or do i have to
> delete in intervalls which takes alot of time??

Using PL/SQL:

declare
  cursor c_table1 is
    select 'x'
    from table1
    where not_unique_id < 10000;
begin
  for r_table1 in c_table1 loop
    delete from table1
    where current of c_table1;
    commit;
  end loop;
end;
/

This would commit after every delete.

If you don't want to commit after every delete but after, say, every 100 deletes (this might improve performance, 100 might not be the right number for optimal performance):

declare
  cursor c_table1 is
    select 'x'
    from table1
    where not_unique_id < 10000;
  v_counter number(3) := 0;
begin
  for r_table1 in c_table1 loop
    delete from table1
    where current of c_table1;
    v_counter := v_counter + 1;
    if counter = 100 then

      commit;
      counter = 0;

    end if;
  end loop;
  commit;
end;
/

Remco
--
rd31-144: 10:35pm up 7 days, 20:52, 9 users, load average: 1.09, 1.26, 1.12 Received on Sat Feb 20 1999 - 15:51:35 CST