Re: staggered/throttled delete

From: Greg Norris <Spikey.McMarbles_at_gmail.com>
Date: Mon, 18 Oct 2004 07:34:12 -0500
Message-ID: <cf0ca31604101805343da6933d@mail.gmail.com>

> Hi All,
> Does any one have an example they can share on performing a large
> delete in small chunks?

Here's a quick & dirty script I put together recently, for purging old data from an audit table. The "purge_date" and "rows_per_set" variables control what data is purged, and how many rows are processed per commit.

It's been tested under 8.1.7.4 and 9.2.0.4, and seemed to perform pretty well... quite adequate for my requirements, at least.

• <begin purge_daudit.sql> ----- set echo off term off verify off

column now new_value rundate noprint
select to_char(sysdate,'yyyymmddhh24miss') now from dual; column now clear

set echo on term on timing on trimout on trimspool on spool purge_daudit-&rundate..log

select count(*) from daudit;

DECLARE

   purge_date                 constant date :=
to_date('2003-09-01','yyyy-mm-dd');
   rows_per_set               constant number := 100000;

   type rowidType is table of ROWID index by binary_integer;
   rowlist                    rowidType;

   cursor c1(cutoff_date date) is
      select rowid from daudit where auditdate < cutoff_date;

BEGIN
   open c1(purge_date);
   loop

      fetch c1 bulk collect into rowlist limit rows_per_set; -- exit when c1%NOTFOUND;

      exit when rowlist.COUNT = 0;

      forall i in rowlist.FIRST..rowlist.LAST
         delete from daudit where rowid = rowlist(i);
      commit;

   end loop;
   close c1;

END;
/

select count(*) from daudit;
spool off
----- <end purge_daudit.sql> -----

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

Received on Mon Oct 18 2004 - 07:29:50 CDT