Sunday, November 7, 2010

delete from large tables using loop+commit


-- delete_large_table.sql
set serveroutput on

-- this script will not use the index on where clause,
-- it will fullscan from the beginning, or add additional rowid
-- rownum does notw work well with index to filter large amount data
declare
-- commit every 'i_commit' rows
i_commit pls_integer := 1000;
-- row counter, 'i_rowcount' rows deleted
i_rowcount pls_integer := 0;

begin
-- define 'infinite' loop
loop
-- delete 'i_commit' rows
delete from sec_owner.large_table
where requesttime< '2010-10-01 01' and rownum <= i_commit;
i_rowcount := i_rowcount + sql%rowcount;
-- now it's time to exit the loop
dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows');
if sql%rowcount = 0 then
commit;
exit;
end if;
commit;
end loop;
-- feedback
dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) || ' rows deleted') ;
dbms_application_info.set_action(action_name => 'deleted '||i_rowcount||' rows (done)');
end;
/





SQL> select action from v$session where username='NCSDBA1' and action is not null;

ACTION
--------------------------------
deleted 130000 rows




SQL> select s.*, n.name
2 from v$sesstat s ,v$statname n
3 where s.statistic# = n.statistic#
4 and n.name = 'user commits'
5* and s.sid=513
SQL> /

SID STATISTIC# VALUE
---------- ---------- ----------
NAME
----------------------------------------------------------------
513 4 130
user commits


SQL>