deleting blob, taking too much time

[sikar]

Hi,

I have a table which stores blob. There are 45,000 records in the table. I have to delete 30,000 records based on some criteria. I have written separate 30k queries, one for deleting one record. It is taking time more than 10 hours to delete all the 30k records. It will be of great help if you can suggest me a fast way to achieve the same.

Thanks

 

[SantaMufasa]

Sikar,

Keep in mind that when you "DELETE" rows, Oracle must duplicate the entire contents of the row in an Rollback/UNDO segment in case you decide to issue a "ROLLBACK" command. A "DELETE", therefore, is volumetrically equivalent to an "INSERT" of the deleted data for rollback purposes.

Alternately, an "INSERT" statement generates virtually no rollback since, if you decided to rollbak an INSERT, you are reverting to.......NOTHING.

So, my suggestion to you, since you have half as many rows to KEEP as to DISCARD, try the following:

1) INSERT the 15,000 remaining rows into a table named SIKAR
2) DROP your original table.
3) RENAME the table, "SIKAR" to the name of the old table.

Keep in mind that if you have indexes/PKs/FKs/other constraints on the original table, you will need to re-create/enable those constraints, but it still should be much faster than your DELETE.

And, as with any major maintenance such as this, be certain that you have backedup/exported the subject table(s) before you start messing with them.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[sikar]

Hi Mufasa,

Thanks a lot for reply. In my case I have separate 30000 queries for deletion and after every query I have commit. So the issue of rollback segment is not there. Since I am deleting row having blob column, it is taking too much time. The overall size of these 30000 rows is approximately 4GB. It is taking approx 12 hours to delete all the 30K rows, one by one, commit at each step. So is there a faster way to do the same?

Thanks

 

[SantaMufasa]

...the issue of rollback segment is not there.

I believe you may be confused, Sikar...If you issue a DELETE command, rollback is an issue. The fact that there are 4GB of deletes makes it a very significant issue since Oracle still must write 4GB of rollback entries, regardless of whether they are individually or collectively committed.


Have you experimented with the suggestion that I made earlier?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]