Re: Deletion from large table

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;
> /
>

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


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