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.

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

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

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 :p

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.

Quote:

---

Originally Posted by alpesh

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

---

That's an interesting concept. Could you elaborate?

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 :D

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

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

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

depending on the size of your table the fastest way to delete these rows would be to:
1) insert into a temp table all the rows you want to keep
2) truncate your table
3) reinsert all data from your temp table

Of course you can only do it this way if there are no FK referencing your table.

That's interesting approach. Isn't it true if the partition of the data to be deleted is bigger that these to be held?
Actually if one uses direct load insert here and suppress the redo, that could really be faster

Agree with both mike9 and bore but... as per original post -OP- doesn't even know how to put together a WHERE clause, he wouldn't understand what you are saying guys.