为什么这个 DELETE 查询在 Oracle 上这么慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16989194/
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
Why is this DELETE query so slow on Oracle?
提问by Mike Christensen
I'm writing some database upgrade scripts, and am running into a query that takes a lot longer than I think it should:
我正在编写一些数据库升级脚本，并且遇到了一个比我想象的要长得多的查询：
DELETE FROM TPM_TASK WHERE TASK_TYPE='System';
This query takes over an hour, and I'm curious what the culprit is.
这个查询需要一个多小时，我很好奇罪魁祸首是什么。
The execution plan is:
执行计划是：
DELETE STATEMENT 899.0 887 57793984 35481 1454721 899 ALL_ROWS
DELETE 1 TPMDBO TPM_TASK
TABLE ACCESS (FULL) 899.0 887 57793984 35481 1454721 1 TPMDBO TPM_TASK FULL TABLE ANALYZED 1
Running:
跑步：
select count(1) FROM TPM_TASK WHERE TASK_TYPE='System';
The plan is:
计划是：
SELECT STATEMENT 92.0 89 14527479 1 7 92 ALL_ROWS
SORT (AGGREGATE) 1 7 1 AGGREGATE
INDEX (FAST FULL SCAN) 92.0 89 14527479 35481 248367 1 TPMDBO TPM_TASK_TASK_TYPE FAST FULL SCAN INDEX ANALYZED
This query is quite fast and gives me 44,202 rows. The total number of rows in the table is 71419. Since I'm deleting over half the rows, I'm thinking Oracle isn't bother to use the index at all on the delete, which is fine. A full scan of 71,000 rows should still only take a few seconds anyhow.
这个查询非常快，给了我 44,202 行。表中的总行数为 71419。由于我删除了一半以上的行，我认为 Oracle 根本不会在删除时使用索引，这很好。无论如何，完整扫描 71,000 行仍然只需要几秒钟。
There are no triggers on this table. There aren't any other tables that have FK constraints on this table, however there are a few views and SQL functions that use this table. The only application that uses this database is our web server, which is shut down during the upgrade - so I don't think there's any locking issues going on. Any other ideas?
此表上没有触发器。没有任何其他表对该表具有 FK 约束，但是有一些视图和 SQL 函数使用该表。唯一使用此数据库的应用程序是我们的 Web 服务器，它在升级期间关闭 - 所以我认为没有任何锁定问题。还有其他想法吗？
回答by Glitch Desire
Baring in mind the considerations you gave, the only thing I can think of is some kind of UNINDEXED REFERENCE CONSTRAINTreferring to this table. Try running this scriptagainst the table, it should give you a report with any unindexed references it finds. There's certainly no reason why this operation should take close to this long.
考虑到你给出的考虑，我唯一能想到的就是某种形式的UNINDEXED REFERENCE CONSTRAINT参考这张表。尝试针对表运行此脚本，它应该会为您提供一个报告，其中包含它找到的任何未索引的引用。当然没有理由为什么这次行动应该花费接近这么长时间。
