-
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
-
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.
-
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
-
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.
-
Originally Posted by PAVB
In this specific case I would recommand the following link
http://www.oracle.com/technology/doc...n/oracle7.html
-
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.
-
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.
-
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
-
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
-
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
|