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>
end loop;
close csr;
end;
/
Date: Wed, 31 Aug 2016 13:28:59 -0500
Message-ID: <CAJueESqOK2NUZeM=P5_WTmhtzZL_zK+jUzWyycD6AJa-aCU_Kg_at_mail.gmail.com>
type rowid_type is table of rowid index by pls_integer; v_rowids rowid_type;
c_batchsize constant number := 1000000;
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-lReceived on Wed Aug 31 2016 - 20:28:59 CEST