-
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.
-
why not just configure your undo settings correctly and do it in one statement
-
... or revise partitioning strategy so purge process can just truncate specific partition.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
why dont they want to - its the best option
ask them why they want a poor solution
-
Originally Posted by Lily_Liu_2004
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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
|