一个delete语句暴慢无比，如何优化？

[hgamezoom]

delete from tbl_survey where n_id not in (select max(n_id) from tbl_survey group by n_human_id)

n_id是主键，n_human_id是人的编码，数字类型，表中有重复，已经建了normal索引。

表中一共60000多条数据，其中n_human_id重复的大概30000条。

这个查询运行了10多分钟都没有出结果，如果我强制加上n_id<10000这样的限制的话，即使只是删除2000条数据，大概也要跑5分钟左右。

实在不知道问题出在哪里？数据库服务器性能没有问题，cpu和内存都在50%以下。

[fengbin041990]

in 是优先查找后面的select，你这种情况是后面时间花得长，exists是优先查前面的。

[fw0124]

select max(n_id) from tbl_survey group by n_human_id的执行计划发出来看看呢？

[碧水幽幽泉]

--删除重复数据的三种写法：
delete from tbl_survey where n_id >  (select min(n_id) from tbl_survey);
delete from tbl_survey where n_id <  (select max(n_id) from tbl_survey);
delete from tbl_survey where n_id <> (select max(n_id) from tbl_survey);

自己慢慢体会吧。

[碧水幽幽泉]

--利用rowid，实现高效删除：
delete from tbl_survey a where a.rowid >  (select min(b.rowid) from tbl_survey b where b.n_id = a.n_id);
delete from tbl_survey a where a.rowid <  (select min(b.rowid) from tbl_survey b where b.n_id = a.n_id);
delete from tbl_survey a where a.rowid <> (select min(b.rowid) from tbl_survey b where b.n_id = a.n_id);

[渐渐成长的小虾米]

not exist 比not in执行效率高
truncate 比 delete执行效率高

[hgamezoom]

执行计划如下：

SELECT STATEMENT, GOAL = ALL_ROWS Cost=718 Cardinality=32056 Bytes=352616
HASH GROUP BY Cost=718 Cardinality=32056 Bytes=352616

VIEW Object owner=ALLTRUST Object name=index$_join$_001 Cost=518 Cardinality=67779 Bytes=745569

HASH JOIN

INDEX FAST FULL SCAN Object owner=ALLTRUST Object name=IDX_HUMAN_ID Cost=190 Cardinality=67779 Bytes=745569

INDEX FAST FULL SCAN Object owner=ALLTRUST Object

name=PK_SURVEY Cost=234 Cardinality=67779 Bytes=745569

[hgamezoom]

[Quote=引用 1 楼 的回复:]

SQL code

--這樣試下呢？
delete tbl_survey
where not exists (select 1 from (select max(n_id) n_id from tbl_survey group by n_human_id) t
where tbl_survey.n_id=t.n_id);
[/Quote]


我用
select count(*) from tbl_survey
where not exists (select 1 from (select max(n_id) n_id from tbl_survey group by n_human_id) t
where tbl_survey.n_id=t.n_id);

非常慢，几分钟也出不了结果，而我用之前写法

select count(*) from tbl_survey where n_id not in (select max(n_id) from tbl_survey group by n_human_id)

立刻就出了结果，为什么呢？

[fw0124]

查询很快，删除慢？
是不是tbl_survey有子表？子表上的外键加上索引了吗？没有的话加上试试？

[tao82274559]

[Quote=引用 8 楼 的回复:]
引用 1 楼 的回复:

SQL code

--這樣試下呢？
delete tbl_survey
where not exists (select 1 from (select max(n_id) n_id from tbl_survey group by n_human_id) t
where tbl_survey.n_id=t.n_id);



我用
select ……
[/Quote]

用not in会创建临时表，你第二次运行就快了。再试试新的内容

[hgamezoom]

[Quote=引用 10 楼 的回复:]

查询很快，删除慢？
是不是tbl_survey有子表？子表上的外键加上索引了吗？没有的话加上试试？
[/Quote]

一共有4个字表，其外键全都加上索引了，似乎效率没有提高。

[人生无悔]

--這樣試下呢？
delete tbl_survey 
where not exists (select 1 from (select max(n_id) n_id from tbl_survey group by n_human_id) t
	where tbl_survey.n_id=t.n_id);

[ORAClE SE]

--一个表只判断一列重复 现在是empno重复--最高效----
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

[一颗向往数据的心丶]

[Quote=引用 6 楼 的回复:]

not exist 比not in执行效率高
truncate 比 delete执行效率高
[/Quote]
弹药注意的是truncate是不可回滚的，delete可会滚。

[linwaterbin]

[Quote=引用楼主 的回复:]
delete from tbl_survey where n_id not in (select max(n_id) from tbl_survey group by n_human_id)

n_id是主键，n_human_id是人的编码，数字类型，表中有重复，已经建了normal索引。

表中一共60000多条数据，其中n_human_id重复的大概30000条。

这个查询运行了……
[/Quote]
我从oracle undo的角度来回答哈：
delete是个极其昂贵的操作哦，它会产生大量的undo数据（最多的），你每删一次oracle都要记录一次。
如果从undo角度来看的话，可以优化的就是控制事务的长度，即用：commit。

[scybcbj]

你先看查询慢不慢
select n_id
from tbl_survey where n_id not in (select max(n_id) from tbl_survey group by n_human_id)

[leech125]

delete * from tbl_survey t1 where where t1.n_id!=
(select max(n_id) from tbl_survey t2
where t1.n_human_id=t2.n_human_id)

不要用group by

[laokang426]

[Quote=引用 1 楼 的回复:]
SQL code
--這樣試下呢？
delete tbl_survey
where not exists (select 1 from (select max(n_id) n_id from tbl_survey group by n_human_id) t
where tbl_survey.n_id=t.n_id);
[/Quote]
这个回答正确。in先执行了in后的子查询SQL，而exists先执行主查询SQL,所以导致效率很慢。