Deleting large amount of data in ORACLE I have to delete larges amount of data by user request, ie, when a user press a button some PL/SQL will be executed to delete a certain set of data. Usually we can say that this set has approximately 10 million of rows. The table will have about 300 million of rows monthly and in this moment I don't know how the housekeeping will work on historic data.
What do you suggest in order to make this the most efficient possible and also to be able to not blow the transaction log files and put the database down?

The delete is something so simple like:

delete from &lt;table&gt; where &lt;field&gt; = &lt;value&gt;


Where this field is not a key in the table.

****** EDIT ******


This is a operation that a user triggers. And has to run after the moment that the user press the button.
The table is partitioned by date (interval of months).
I made an edit in the original description. There will be an estimated 300M of rows monthly, 10M per day. So you can look at this as 30 loadings of 10M of rows per day that will be in the same partition (partition for that month), and I want to delete one (or more) specific loadings for that month (partition).
We are just beginning so what I can say is just estimated values.


Thanks in advance.
If you want to be able to delete this data with the maximum efficiency, and you do not need to be able to roll the transaction back, then change the partitioning scheme to be based on days.

If you want to be able to optionally recover that data, then you could still partition it, but could remove it from the table using a partition exchange with a newly created table. You can then drop that table at a later date.

Re-partitioning aside, deleting 10 million rows is always going to require extensive undo logging, because a rollback operation has to recreate those rows. The redo would not be so extensive.

But it is never going to be a lightweight operation.

p.s. There's no such thing as blowing a transaction log, or putting a database down -- if you use informal, slang terminology to describe a problem then you're not describing it accurately, and you're inviting imprecise responses.