Delete query is slow

[sunith]

I have a simple delete query like

delete
from table_name where (col1=:b0
and col2=:b1)

This delete would actually delete around 100 records out of 4 Lakh records.
This delete is slow.

The columns used are the part of the primary key which is a combination of 4 cols. There is an index also on this primary key.

In a batch this query is called for around 5000 times which makes the batch run slow.
Pls help me to make this query run fast..

[chandu031]

Hi,

I was facing a similar problem. The deletes are very slow in almost all the databases that I have worked with. I don't know the actual reason for this. Anyways for my problem I did a workaround , and marked those rows for deletion instead of actually deleting them. I used a flag like "IS_DELETED"which gets updated for all those rows which are to be deleted (Updates are much faster than deletes). You will just have to have one more filter on "IS_DELETED" flag for all subsequent queries referncing this table. This worked fine in my case. Try doing this if this is feasible for you....

Can anyone suggest an alternate way of optimizing the Delete query??