• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

does delete statement lock the table

does delete statement lock the table and is there any way to speed up the delete process for large tables , other than creating indexes on the condition fields.
1 Solution
DavidSenior Oracle Database AdministratorCommented:
I believe (from memory) they create row locks but not table-level by default.  Is there referencial integrity with other tables?  Are you doing multiple or single commits?
The general advice these days (10g and higher) is to rename the source table, then create a (new) original table by copying in the records you wish to keep.  The technique is commonly referred to as CREATE TABLE AS SELECT... (CTAS).  Other options are to disable unnecessary indexes and constraints.  Check also if you're firing triggers.
Depending upon your ease of restarting, some suggest altering the table and indexes to nologging.
Hope this helps!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now