471,898 Members | 1,886 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,898 software developers and data experts.

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 1418

This discussion thread is closed

Replies have been disabled for this discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.