I have another trick up my sleeve, which wasn't mentioned. Basically,
you can create a single table hash cluster containing the large table in
question. All the rows that should be deleted will reside near each
other, so the number of IO requests will be much smaller. However, this
works the best when the row size is small.
On 08/31/2016 02:28 PM, Adric Norris wrote:
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;
/
