-
batch delete with commit size
DBAs,
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||')
where trunc(months_between(sysdate,pmt_archive_date))
> '||retention_months||'
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|