SQL oracle 删除查询花费太多时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1327190/
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
oracle delete query taking too much time
提问by Ajay
I have a query like
我有一个类似的查询
DELETE from tablename where colname = value;
which takes awfully long time to execute. What could be the reason? I have an index on colname.
这需要很长时间才能执行。可能是什么原因？我在 colname 上有一个索引。
回答by Vincent Malgrat
There could be several explanations as to why your query takes a long time:
关于为什么您的查询需要很长时间，可能有几种解释：
- You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: issue
SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT, - There could be a
ON DELETE TRIGGERthat does additional work, - Check for
UNINDEXED REFERENCE CONSTRAINTSpointing to this table (there is a script from AskTomthat will help you determine if such unindexed foreign keys exist).
回答by Wael Dalloul
it could be that your table is related to multiple tables have a huge row count.
可能是您的表与多个表相关，行数很大。
回答by APC
How selective is that index? If your table has one million rows and that value hits one hundred and fifty thousand of them then your index is useless. In fact it may be worse than useless if it is actually being used. Remember, a DELETE is a like a SELECT statement: we can tune its access path.
该指数的选择性如何？如果您的表有 100 万行并且该值达到了 15 万行，那么您的索引就没有用了。事实上，如果它被实际使用，它可能比没用更糟糕。请记住，DELETE 类似于 SELECT 语句：我们可以调整其访问路径。
Also, deletes take up a lot of undo tablespace, so you might be suffereing from contention, if the system is experiencing heavy use. In a multi-user system another session might have a lock on the rows(s) you want to delete.
此外，删除会占用大量撤消表空间，因此如果系统正在大量使用，您可能会遇到争用问题。在多用户系统中，另一个会话可能锁定要删除的行。
Do you have ON DELETE triggers? Do you have ON DELETE CASCADE foreign key constraints?
你有 ON DELETE 触发器吗？你有 ON DELETE CASCADE 外键约束吗？
Edit:Given all that you say, and especially the column in question being the primary key so you are attempting to delete a single row, if it is taking a long time it is much more likely that some other process or user has a lock on the row. Is anything showing up in V$LOCK?
编辑：鉴于您所说的所有内容，尤其是有问题的列是主键，因此您试图删除一行，如果需要很长时间，则其他进程或用户更有可能锁定行。有什么显示V$LOCK吗？
回答by q23main
So I'll just post my experience. Might be helpful for someone.
所以我就发一下我的经验吧。可能对某人有帮助。
The query
查询
delete from foo
where foo_id not in (
select max(foo_id) from foo group by foo_bar_id, foo_qux_id
);
took 16 sec. deleting 1700 records from 2300 total in table foo.
花了 16 秒。从表中的 2300 条记录中删除 1700 条记录foo。
I checked all the indexes on foreign keys as directed in other answers. That did not help.
我按照其他答案中的指示检查了外键上的所有索引。那没有帮助。
Solution:
解决方案：
Changed the query to
将查询更改为
delete from foo
where foo_id in (
select foo_id from foo
minus
select max(foo_id) from foo group by foo_bar_id, foo_qux_id
);
I've changed not into inand used minusto achieve correct result.
我已更改not in为in并用于minus获得正确的结果。
Now the query executes in 0.04 sec.
现在查询在 0.04 秒内执行。
回答by Mostafa Vatanpour
There is a significant difference between Oracle and mysql :
Oracle 和 mysql 之间存在显着差异：
Oracle does not create index automatically for foreign keys but mysql does. Then if you have some parent table that you may execute delete command on it then you must create index on foreign keys in child tables otherwise the delete command on parent table will be very very slow if child tables has a lot of rows, because it must surf all records of child table perdeletion of any parent records.
Oracle 不会自动为外键创建索引，但 mysql 会。然后，如果您有一些可以对其执行删除命令的父表，那么您必须在子表中的外键上创建索引，否则如果子表有很多行，父表上的删除命令将非常慢，因为它必须每次删除任何父记录时都会浏览子表的所有记录。
Then be careful when you want to delete from parent table in Oracle database.
然后当您要从 Oracle 数据库中的父表中删除时要小心。
回答by Shyju
Does your table holds more number of records ?
Is there some recursive programs(some nested loops etc..) running on the database server ?
Check network problems if database server is on different machines ?
您的表是否包含更多记录？
是否有一些递归程序（一些嵌套循环等）在数据库服务器上运行？
如果数据库服务器在不同的机器上，检查网络问题？
回答by Dave Costa
If something's slow, and you don't know why, trace it and find out.
如果某些事情很慢，而您不知道为什么，请跟踪它并找出答案。
How do I troubleshoot performance problems with an Oracle SQL statement
