Bulk Delete

[alpesh]

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

[PAVB]

This is too much lazyness, I refuse to be an accessory to this crime.

[alpesh]

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

[PAVB]

http://www.oracle.com/pls/db102/homepage ... and, stay away from production

[mike9]

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

[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.

[PAVB]

...I mean it will hit my performance...

That's an interesting concept. Could you elaborate?

[dbasan]

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

[Bore]

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

[liakat]

u can also go........................

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