469,578 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

how to determine what locking behavior the query optimizer chooses?

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.


delete from

Jul 20 '05 #1
0 1363

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.