哪位大侠能帮我优化一下这个SQL语句

[xiao828118]

DELETE test1 A
WHERE A.dj<(SELECT MAX(dj) FROM test2 WHERE khdm=A.khdm AND spdm=A.spdm)


语句中的test1和test2表结构几乎一样，两张都是临时表在存储过程中使用，
其中两张表的khdm和spdm是有索引的，而DJ是没有索引，
DJ为1，2，3，4，5，的1位数字。
两张表都是由khdm和spdm唯一确定一条记录，每张表的记录数一般有200多万条，单执行这一句话
就要30多分钟，不知哪位高手能不能优化一下，这种速度太慢了。

[gelyon]

DELETE test1 A 
 WHERE EXISTS(SELECT 1 FROM test2 WHERE khdm=A.khdm AND spdm=A.spdm AND A.dj<dj)

[真的不知道取什么名]

--这样试试
delete test1 a
where not exists(select 1 from test2 b 
                 where a.khdm=b.khdm and a.spdm=b.spdm and a.dj>=b.dj)

[真的不知道取什么名]

如果删除的数据量比较多的话 先把索引禁用了

http://blog.csdn.net/robinson1988/archive/2010/09/06/5867388.aspx
看看这个博客 希望对你有帮助

[xiao828118]

谢谢，我试试，一会告诉你结果。

[dut703]

[Quote=引用 2 楼 zhuomingwang 的回复:]

SQL code
--这样试试
delete test1 a
where not exists(select 1 from test2 b
where a.khdm=b.khdm and a.spdm=b.spdm and a.dj>=b.dj)
[/Quote]
这个不错，如果删除数据多的话，回滚段会有很大压力，可以批量删除，每删除5万或者更多就提交一次。

[njlywy]

DELETE test1 A WHERE EXISTS(SELECT 1 FROM test2 WHERE khdm=A.khdm AND spdm=A.spdm AND A.dj<dj)

数据庞大的话禁用索引在执行…

[UPC子夜]

楼主先根据前边的建议试试 把<换成exists 再关掉索引
但是 数据太多的话 无论怎么优化都不行的
你可以分段删除 一次删掉1万 具体数字可以根据你的数据库的效率决定

[心中的彩虹]

[Quote=引用楼主 xiao828118 的回复:]
DELETE test1 A
WHERE A.dj<(SELECT MAX(dj) FROM test2 WHERE khdm=A.khdm AND spdm=A.spdm)


语句中的test1和test2表结构几乎一样，两张都是临时表在存储过程中使用，
其中两张表的khdm和spdm是有索引的，而DJ是没有索引，
DJ为1，2，3，4，5，的1位数字。
两张表都是由khdm……
[/Quote]

既然是临时表 直接truncate 然后在插入

不是临时表
DELETE test1 A
WHERE A.dj<(SELECT MAX(dj) FROM test2 WHERE khdm=A.khdm AND spdm=A.spdm)

改成这样试试

DELETE test1 A
WHERE exists<(SELECT 1 FROM test2 b WHERE b.khdm=A.khdm AND b.spdm=A.spdm
and a.dj>b.dj)

如果删除的数据量差不多接近全部删除可以考虑 禁用索引,然后在重建，否则没必要删除索引
alter index idxname unsable/rebuild

[wolianlin1987]

DELETE FROM
test1

WHERE ROWID IN (
SELECT ROWID FROM (
SELECT
A.*
ROW_NUMBER()OVER(PARTITION BY A.khdm ,A.spdm ORDER BY DJ DESC) R
FROM test1 A,test1 B
WHERE khdm=A.khdm AND spdm=A.spdm) C WHERE C.R=1)

[wolianlin1987]

删除ROWID应该是比较快的，可以改善下不用IN 用exists

[10975037]

oracle 对delete的数据要写入重做日志中，所以慢。
我感觉你的删除数据一定很大。所以很慢 。
建议改变做法 ，先truncate 截断表中的数据。在把你需要保留的数据insert into select 进来

[hezhanyan]

不错 楼上的意见挺好的

[zhuhui_id]

可以insert into一个临时表，TRUNCATE原来表，在把数据导过来。