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 <table> where <field> = <value>

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.

  • What you do not tell us is the frequency of this operation. That could be a key to a (or more) solution. You say 30m rows monthly, but you do not say how much it has currently. Is the table partitioned? Does the delete has to happen immediately? can it be deferred to non-business hours?
    – Raj
    Commented Apr 6, 2017 at 12:18
  • If you have to delete 1/3 of entire table I propose partitioning Commented Apr 6, 2017 at 12:33
  • The table will have about 300M of rows in each monthly partition. That is a possibility, change the partition method... But we are looking for some other solution. Commented Apr 6, 2017 at 12:35
  • Same issue? community.oracle.com/thread/4034576
    – Raj
    Commented Apr 7, 2017 at 17:35

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.