Delete statement was very slow in Oracle
oracle, sql
Solution
There can be many reasons:
- Server load (unlikely because the `SELECT` is fast)
- Triggers (see here how to list them for a table).
- Foreign keys (List of foreign keys and the tables they reference)
- A lot of data in each row (`LOB`s, many columns).
- Someone is locking rows in the table that you'd like to delete (or the whole table). See this blog post how to list locks. This discussion might also help.
If the foreign keys are the problem, the usual solution is to add indexes on the foreign column: For each delete, Oracle needs to check whether this would violate a foreign key relation.
Problem
I have a table with about 100k records and I want to delete some rows, The problem is that the `DELETE` statement is running very slowly - it didn't finish in 30 minutes. But the `select` statement was return in 1 second. The `SELECT` statement is as follows: ``` select * from daily_au_by_service_summary where summary_ts >= to_date('09-04-2012','dd-mm-yyyy') order by summary_ts desc; ``` and `DELETE` statement is as follows: ``` delete from daily_au_by_service_summary where summary_ts > to_date('09-04-2012','dd-mm-yyyy'); ``` This table have the only index at `summary_ts`. What could be the reason? EDIT: The problem had been resolved after I killed the sessions which locks the table, thanks all for the help. ``` SESSION_ID ORACLE_USERNAME OS_USER_NAME OBJECT OWNER OBJECT_NAME OBJECT_TYPE LOCKED_MODE ---------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- ----------- 213 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 203 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 202 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 190 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 189 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 188 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY TABLE 3 187 T03RPT elou T03RPT DAILY_AU_BY_SERVICE_SUMMARY ```