How-to purge data by delete

Sometimes, we need to delete tons of rows from a big table. Having a procedure to do that may make our life easier, so here it is.

create or replace procedure purge_by_delete (
p_table_name varchar2,
where_clause varchar2,
p_table_owner varchar2 := user,
hints varchar2 := 'purge_by_delete',
batch_size number := 1000
  type t_rid is table of urowid index by PLS_INTEGER;
  rids t_rid;
  total_deleted_rows   number    := 0;
  rows_to_delete       integer   := 0;
  v_cnt                number    := 0;
  v_sql                varchar2(2000);
  select count(*) into v_cnt from user_tables where table_name='GLOBAL_TEMP_DELETE_PER_ROWID';
  if v_cnt = 0 then
    execute immediate 'create global temporary table GLOBAL_TEMP_DELETE_PER_ROWID (rid urowid) on commit preserve rows';
  end if;
  execute immediate 'truncate table GLOBAL_TEMP_DELETE_PER_ROWID';

  execute immediate 'alter session set db_file_multiblock_read_count=128';
  execute immediate 'alter session set sort_area_size=80000000';
  execute immediate 'alter session set hash_area_size=80000000';

  v_sql :=         'insert into GLOBAL_TEMP_DELETE_PER_ROWID select /*+ ' ;
  v_sql := v_sql || hints || ' */ rowid rid from ' || p_table_owner || '.' || p_table_name ;
  v_sql := v_sql || ' t where (' || where_clause || ') order by rowid ';
  execute immediate v_sql;

  execute immediate 'select count(*) from GLOBAL_TEMP_DELETE_PER_ROWID' into rows_to_delete;
  dbms_output.put_line ( 'rows to delete: ' || rows_to_delete );
  open c_rid for 'select rid from GLOBAL_TEMP_DELETE_PER_ROWID';
      fetch c_rid bulk collect into rids limit batch_size;
      exit when rids.count = 0;
      forall i in rids.first..rids.last
         execute immediate 'delete from '||p_table_owner||'.'||p_table_name||' where rowid=:1' using rids(i);
      total_deleted_rows := total_deleted_rows + SQL%ROWCOUNT;
      dbms_application_info.set_client_info('deleted '||total_deleted_rows||' rows out of '||rows_to_delete||' on table '||p_table_owner||'.'||p_table_name);
  end loop;
  close c_rid;
  dbms_output.put_line('deleted '||total_deleted_rows||' rows from table '||p_table_owner||'.'||p_table_name);
  if rows_to_delete = total_deleted_rows then
      dbms_output.put_line ( 'Congrats: delete finished successfully, rows to delete matched to rows to delete.') ;
      dbms_output.put_line ( 'Warning: rows deleted did not match to rows to delete.' ) ;
   end if;

Test it:

SQL> exec purge_by_delete('RT', 'rownum<=10000');
rows to delete: 10000
deleted 10000 rows from table PERFSTAT.RT
Congrats: delete finished successfully, rows to delete matched to rows to delete.

PL/SQL procedure successfully completed.

You may need to grant create table to a user to create the temporary table dynamically or you can create it beforehand manually. In addition, you can monitor the deleting progress by this SQL:

select client_info from v$session where client_info like '%delete%';

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How-to purge data by delete

  1. Pingback: Case: optimize a delete job « Oracle Explorer: Standing on the shoulders of giants

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: