删除太慢了，请问如何加快?

显示全部楼层 · 2005-11-4 15:12:46

删除重复记录前,先查询了一下要删除的有多少行:
select count(*)
from tab1 a
where a.rowid(select max(rowid) from demo b where

b.object_id=a.object_id);

耗时：几个小时以上
方法二： delete from demo where rowid in

(select rid from

(select rowid rid,row_number() over(partition by object_id order by rowid) rn

from demo)

where rn1 );
耗时：30秒
方法三： create table demo2 as

select object_id,owner... from

(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)

where rn = 1;

truncate table demo; insert into demo select * from demo2; drop table demo2;
共耗时： 10秒，适合大数据量的情况，产生更少回滚量；
..
[/php]

千问 · 2005-11-4 15:12:46

建议用方法二

千问 · 2005-11-4 15:12:46

谢谢~！我刚搜了一下，发现还有种办法：
delete cz where (c1,c10) in (select c1,c10 from cz group by c1,c10 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10 having count(*)>1);

千问 · 2005-11-4 15:12:46

最初由 coohoo 发布
[B]谢谢~！我刚搜了一下，发现还有种办法：
delete cz where (c1,c10) in (select c1,c10 from cz group by c1,c10 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10 having count(*)>1); [/B]
这些方法效率都是很低的
建议还是使用oracle 的分析函数比较快一点的

千问 · 2005-11-4 15:12:46

要考虑删除的重复记录占总记录数的比例。

千问 · 2005-11-4 15:12:46

方法二： delete from demo where rowid in

(select rid from

(select rowid rid,row_number() over(partition by object_id order by rowid) rn

from demo)

where rn1 );
对于此方法，如果判断两个字段是否重复，我这样写：
delete from demo where rowid in

(select rid from

(select rowid rid,row_number() over(partition by object_id,object_id2 order by rowid) rn

from demo)

where rn1 );
就是增加了第2个字段object_id2，但发现结果不对。应该怎么写？

千问 · 2005-11-4 15:12:46

oracle的删除和更新操作本来就是很慢的，没有更好的办法，如果你预先分区还好一些

千问 · 2005-11-4 15:12:46

如果没有大的影响，建议 CTAS

千问 · 2005-11-4 15:12:46

没有哪种是最优的，
根据数据(分布，多少等等)情况，上面几种方式会用不一样的表现。