一个delete语句暴慢无比,如何优化?

hgamezoom 2012-08-16 11:21:14
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%以下。



...全文
7020 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
laokang426 2012-09-10
  • 打赏
  • 举报
回复
[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,所以导致效率很慢。
leech125 2012-09-10
  • 打赏
  • 举报
回复
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
scybcbj 2012-09-10
  • 打赏
  • 举报
回复
你先看查询慢不慢
select n_id
from tbl_survey where n_id not in (select max(n_id) from tbl_survey group by n_human_id)

linwaterbin 2012-08-18
  • 打赏
  • 举报
回复
[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。
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 的回复:]

not exist 比not in执行效率高
truncate 比 delete执行效率高
[/Quote]
弹药注意的是truncate是不可回滚的,delete可会滚。
ORAClE SE 2012-08-17
  • 打赏
  • 举报
回复
--一个表只判断一列重复 现在是empno重复--最高效----
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
人生无悔 2012-08-16
  • 打赏
  • 举报
回复

--這樣試下呢?
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);
hgamezoom 2012-08-16
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 的回复:]

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

一共有4个字表,其外键全都加上索引了,似乎效率没有提高。
tao82274559 2012-08-16
  • 打赏
  • 举报
回复
[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会创建临时表,你第二次运行就快了。再试试新的内容

fw0124 2012-08-16
  • 打赏
  • 举报
回复
查询很快,删除慢?
是不是tbl_survey有子表?子表上的外键加上索引了吗?没有的话加上试试?
hgamezoom 2012-08-16
  • 打赏
  • 举报
回复
[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)

立刻就出了结果,为什么呢?
hgamezoom 2012-08-16
  • 打赏
  • 举报
回复
执行计划如下:

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
  • 打赏
  • 举报
回复
not exist 比not in执行效率高
truncate 比 delete执行效率高
碧水幽幽泉 2012-08-16
  • 打赏
  • 举报
回复
--利用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);
碧水幽幽泉 2012-08-16
  • 打赏
  • 举报
回复
--删除重复数据的三种写法:
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);

自己慢慢体会吧。
fw0124 2012-08-16
  • 打赏
  • 举报
回复
select max(n_id) from tbl_survey group by n_human_id的执行计划发出来看看呢?
fengbin041990 2012-08-16
  • 打赏
  • 举报
回复
in 是优先查找后面的select,你这种情况是后面时间花得长,exists是优先查前面的。

17,140

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧