oracle删除大量重复数据

2.删除重复记录的几种方法：


(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候，用以下语句效率会很高)：


 SQL>delete cz where (c1,c10,c20) in (select c1,c10,
c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);


SQL>delete cz where rowid not in
(select min(rowid) from cz group by c1,c10,c20);
(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：


 SQL>delete from cz a where a.rowid!=(select max(rowid)
 from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);


SQL>delete from cz a where a.rowid<(select max(rowid) 
from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);


SQL>delete from cz a where rowid <(select max(rowid) 
from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
(3).适用于有少量重复记录的情况(临时表法)：


 SQL>create table test as select distinct * from cz; 
(建一个临时表test用来存放重复的记录）


SQL>truncate table cz; (清空cz表的数据，但保留cz表的结构）


SQL>insert into cz select * from test; 
(再将临时表test里的内容反插回来）




create index index_ts_top on  ts_top_dom(top_dom);


delete ts_top_dom where top_dom in (select top_dom from ts_top_dom group by top_dom  having count(*)>1) and rowid not in
(select min(rowid) from ts_top_dom group by top_dom having count(*)>1);


delete ts_top_dom where rowid not in
(select min(rowid) fromts_top_dom group by top_dom );


select distinct TOP_DOM from TOP_DOM


truncate table ts_top_dom;//快速删除表中数据