batch delete with commit size

[Lily_Liu_2004]

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.

[davey23uk]

why not just configure your undo settings correctly and do it in one statement

[PAVB]

... or revise partitioning strategy so purge process can just truncate specific partition.

[Lily_Liu_2004]

Thanks for the reply.

The customers don't want to do the masive deletion and commit at one time.
The data to purge can be hughe.

Also, we do truncate the qualified oldest partition data.
Besides that, there are data which is qualified to purge across th
other partitions.
So we have to do the delete besides the partition truncate.

[davey23uk]

why dont they want to - its the best option

ask them why they want a poor solution

[PAVB]

Also, we do truncate the qualified oldest partition data.
Besides that, there are data which is qualified to purge across th
other partitions.
So we have to do the delete besides the partition truncate.

How about running a single transaction delete per partition? that would segment the delete process.

By the way... what's the percentage of rows expected to be deleted? is it 50% or more?