batch delete with commit size
I am working on a purge project and need to code pl/sql to purge the large tables
Need some help with massive delete with commit size.
The coding should satisfy ;
1)the commit size e.g. 5000
2)might use the bulk collect to improve the performance.
My situation is ;
all the real delete command will like this: ( it is dynamic query. The table name and the partition is dynamic, not static)
execute immediate '
delete from '||purge_table_fq||'
where pmt_id in (
select pmt_id from '||root_table_fq||' partition('||oldest_partition||')
and pmt_id not in (select pmt_id from bm.cbpayment_inquiry)
How can I code with commit size( if possible, also use the bulk collect)
If anyone has the similar coding experience, would you please provide a sample code?
Thanks a lot.