how to determine what locking behavior the query optimizer chooses?

[Dave C.]

Hi there,

We have some code that empties a table. Originally, the code used "delete
from", but I am told that this method was either too slow, or held a lock
for too long (this was a long time ago and I wasn't involved at that
point, so I'm not 100% sure exactly the nature of this problem). So the
code was changed to "truncate table". Now however, this table is being
replicated, so truncate table is no longer an option. I need to change the
code back to using delete rather than truncate. However, I want to avoid
the original problem. I was thinking of either two solutions; I can give a
row or page level locking hint, or I can delete groups of records at a
time, rather than all at once.

Basicially, I'd like to determine what behavior the query optimizer will
use for locking with a statement "delete from mytable" without any hints.
Then I will know whether specifying a hint will help, or whether it's
already using finer or coarser granularity and thus hints would be
useless. If possible, I'd rather be pointed towards a general purpose
solution, rather than the specific answer for this particular query so
that I can find out the next time myself... Also, a general rule of thumb
for the best way to efficiently clear out a table without excluding other
processes would be nice.

Thanks for any suggestions.

Dave


delete from