DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: batch delete with commit size

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why not just configure your undo settings correctly and do it in one statement

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ... 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.

  4. #4
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why dont they want to - its the best option

    ask them why they want a poor solution

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Lily_Liu_2004 View Post
    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
  •  


Click Here to Expand Forum to Full Width