Oracle 中的删除语句非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10101484/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete statement was very slow in Oracle
提问by Elvis Lou
I have a table with about 100k records and I want to delete some rows, The problem is that the DELETEstatement is running very slowly - it didn't finish in 30 minutes. But the selectstatement was return in 1 second.
我有一个包含大约 100k 条记录的表，我想删除一些行，问题是该DELETE语句运行非常缓慢 - 它没有在 30 分钟内完成。但是select语句在1秒内返回。
The SELECTstatement is as follows:
该SELECT声明如下：
select * from daily_au_by_service_summary
where summary_ts >= to_date('09-04-2012','dd-mm-yyyy')
order by summary_ts desc;
and DELETEstatement is as follows:
和DELETE声明如下：
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.
该表在 处具有唯一索引summary_ts。
What could be the reason?
可能是什么原因？
EDIT: the table was locked by many sessions:
编辑：该表被许多会话锁定：
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
How to kill these sessions?
如何杀死这些会话？
The problem had been resolved after I killed the sessions which locks the table, thanks all for the help. –
在我杀死锁定表的会话后问题已经解决，感谢大家的帮助。——
回答by Aaron Digulla
There can be many reasons:
可能有很多原因：
- Server load (unlikely because the
SELECTis 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 (
LOBs, 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 discussionmight also help.
- 服务器负载（不太可能，因为
SELECT速度很快） - 触发器（请参阅此处如何为表格列出它们）。
- 外键（外键列表及其引用的表）
- 每行（
LOBs，许多列）中有很多数据。 - 有人锁定了您要删除的表（或整个表）中的行。请参阅此博客文章如何列出锁。这个讨论也可能有帮助。
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.
如果外键是问题，通常的解决方案是在外列上添加索引：对于每次删除，Oracle 需要检查这是否会违反外键关系。
回答by Hubert Sch?lnast
To delete means to change the table's content. And this means, that after each deleted row all indexes must be updated and all foreign-key references must be checked. This can take a very long time!
删除意味着更改表格的内容。这意味着，在每个删除的行之后，必须更新所有索引，并且必须检查所有外键引用。这可能需要很长时间！
Maybe this helps:
也许这有帮助：
Make a copy of that table without any references, triggers and additional indexes. Then do this:
制作该表的副本，不带任何引用、触发器和附加索引。然后这样做：
insert into new_table (field1, field2, ...) values (
select field1, field2, ...
from daily_au_by_service_summary
where summary_ts < to_date('09-04-2012','dd-mm-yyyy')
);
If the fields in the tabels are defined in identical order, this might work too:
如果表格中的字段以相同的顺序定义，这也可能有效：
insert into new_table values (
select *
from daily_au_by_service_summary
where summary_ts < to_date('09-04-2012','dd-mm-yyyy')
);
After that:
在那之后：
truncate daily_au_by_service_summary
and then:
进而：
insert into daily_au_by_service_summary (field1, field2, ...) values (
select field1, field2, ...
from new_table;
);
New Table is not needed any longer:
不再需要新表：
drop new_table;
回答by James Wiseman
Obviously, a delete operation will take longer than a select, but that doesn't account for the difference you see.
显然，删除操作比选择需要更长的时间，但这并不能说明您看到的差异。
It sounds like additional code is being run upon the delete, which indecates there may be triggers on the table that are also running. Can you check this?
听起来像是在删除时正在运行其他代码，这表明表上可能还有正在运行的触发器。你能检查一下吗？
回答by bonsvr
When DML operations take a long time, create new table with the remaining rows and drop the previous table, instead of deleting.
当 DML 操作需要很长时间时，用剩余的行创建新表并删除以前的表，而不是删除。
I mean,
我的意思是，
create table NEW_TABLE as
select * from daily_au_by_service_summary
where summary_ts <= to_date('09-04-2012','dd-mm-yyyy');
This will be faster, especially when you are deleting a substantial number of rows. (%10 of total rows, for example.)
这会更快，尤其是当您要删除大量行时。（例如，占总行数的 %10。）
