April 3, 2004 at 11:25 pm
I have to delete daily large amount of data older than 60 days. During deleting, I found other user has to wait until the delete is committed.
1)Is there anyway to speed up the delete process like no logging?
2)Is there anyway for other user to view data during the delete process? Oracle allows this using the rollback segment to get read consistency.
Thanks for your help.
April 4, 2004 at 8:51 am
Well there are a few ways. Maybe you neede to move you delete process to an off time or day using a job to handle the delete. But you can in SELECT statements try the NOLOCK hint or use the SET TRANSACTION ISOLATION LEVEL statement.
April 6, 2004 at 8:49 am
Use ROWLOCK hint on the DELETE and NOLOCK hint on the SELECT.
Setting transaction isolation level to read uncommitted is different to using NOLOCK, since it may allow your users to retrieve data which is in the process of being deleted. This could easily lead to later errors if your user tries to update this data.
NOLOCK will minimise the impact of locking, while still maintaining data consistency.
If the above doesn't help, you might consider splitting your delete into a number of steps, deleting only part of the data with each statement. The overall execution time will probably increase, but your users will be locked out for smaller periods of time.
Regards
Rob
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply