Bulk Delete
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Bulk Delete

  1. #1
    Join Date
    Nov 2006
    Posts
    23

    Bulk Delete

    Hi Guys,
    My database is 7.3.4 on aix. I want to do a bulk delete for reorg. I am wondering is there any script for that and if it is then kindly let me know where can i put my constraint for specific dates to be purged.

    Thanks a lot in advance

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    This is too much lazyness, I refuse to be an accessory to this crime.
    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.

  3. #3
    Join Date
    Nov 2006
    Posts
    23
    ok tell me some hints I can create but atlease some hints can really help me out..........i know that pl/sql is really good but can you help me out with this

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    http://www.oracle.com/pls/db102/homepage ... and, stay away from production
    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.

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Quote Originally Posted by PAVB
    http://www.oracle.com/pls/db102/homepage ... and, stay away from production
    In this specific case I would recommand the following link

    http://www.oracle.com/technology/doc...n/oracle7.html

  6. #6
    Join Date
    Nov 2006
    Posts
    23
    thanks a lot guys for the link but I have already visited both the sites and nothing helpful have been found. I want a script to delete more than 1 millon rows........and a simple sql query will not do that........I mean it will hit my performance...........is there any pl/sql script doing commit after ever 1000 or 2000 rows and in this way my performance cannot be hit.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by alpesh
    ...I mean it will hit my performance...
    That's an interesting concept. Could you elaborate?
    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.

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by alpesh
    thanks a lot guys for the link but I have already visited both the sites and nothing helpful have been found. I want a script to delete more than 1 millon rows........and a simple sql query will not do that........I mean it will hit my performance...........is there any pl/sql script doing commit after ever 1000 or 2000 rows and in this way my performance cannot be hit.
    good you got the logic,

    now delete those 1000 rows, use a date column in the where condition and then commit after delete. Do this as many times until you reach the required.

    You can also script this as a batch file and run after business hours to complete before business start hour the next day.

    good luck

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Actually, I believe the more commits you do, the more your performance will suffer.
    Each commit forces the log writer to write so when log writer writes more often, it does more job and gets slower.
    Rollback segments is what you should be concerned about with the large transactions and if they will be able to handle the transaction
    If I was you, I would test with different transaction sizes, using small parts of the data, so that I know how really my system performs better, and then I would go for deleting the real part of the data.
    I believe that definitely should be done off hours

    Regards

  10. #10
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44
    u can also go........................

    http://download.oracle.com/docs/cd/B...htm#LNPLS00602

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