Home -> Mailing Lists -> Oracle-L -> Re: Deletion from large table

Re: Deletion from large table

From: Adric Norris <landstander668_at_gmail.com>
Date: Wed, 31 Aug 2016 13:28:59 -0500
Message-ID: <CAJueESqOK2NUZeM=P5_WTmhtzZL_zK+jUzWyycD6AJa-aCU_Kg_at_mail.gmail.com> 








In that sort of situation -- assuming that the CTAS / drop / rename
suggestion can't be used -- I generally drop to PL/SQL bulk operations.



declare


   type rowid_type is table of rowid index by pls_integer;
   v_rowids    rowid_type;



   c_batchsize constant number := 1000000;



   cursor csr is

      select rowid from big_table
         where ...;





begin


   open csr;


   loop

      fetch csr
         bulk collect into v_rowids
         limit c_batchsize;
      exit when v_rowids.COUNT = 0;

      forall c in 1..v_rowids.COUNT
         delete from big_table
            where rowid = v_rowids(c);
      commit;




   end loop;


   close csr;


end;


/


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


Received on Wed Aug 31 2016 - 20:28:59 CEST












Original text of this message