Tuesday, March 24, 2020

Delete large number of rows by chunking with BULK COLLECT and FORALL in PL/SQL block

Just putting this here for my reference or anyone who is looking for logic.

This approach will be best if following requirements are  meet- 

a)  Partition wise data remove not possible
b)  Deleting around 5-20%  (less % ) of data from a very large table .

 if you are planing to delete most of the record of a table then you can go for approach of CTAS (Create Table As Select). but quickest approach will be deleting data based on Partition if possible. 

Consideration for best performance: 

 a)  Need to disable referential integrity constraints if any (if is not OLTP system)
 b)  Need to mark all extra indexes unusable (if is not OLTP system)
 c)  Stop transactions while deleting if possible.



1)  Drop existing table if any

  SQL> drop table halim_table ;

2)  Creating a large table 

 SQL> create table halim_table
    as select a.* from all_Objects a,
       ( select 1 from dual connect by level <= 100 );


3) Delete code block using forall 


--set serveroutput on
--set timing on
--set time on

SQL>

declare
   type record_list is table of rowid index by pls_integer;
      l_list record_list;
      no_of_time number :=0 ;
      no_of_records number :=10000 ; ----change this value if you want to reduce or increase
   begin
     select rowid bulk collect into l_list
     from halim_table 
     where owner = 'SYSTEM';
     dbms_output.put_line('Total Records: '||l_list.count);
     for x in 0 .. trunc(l_list.count / no_of_records) loop
      --dbms_output.put_line('x:'||' '||x||' '||'l_rid: '||l_rid.count);
       forall i in x*no_of_records+1 .. least( (x+1)*no_of_records, l_list.count )
         delete from halim_table where rowid = l_list(i);
         no_of_time:=x+1 ; 
       dbms_output.put_line('Deleted'||'('||no_of_time||') '||sql%rowcount||' rows');
     end loop;
     commit;
   end;

No comments: