oracle中删除重复数据

在数据迁移的时候，会涉及到重复数据的过滤以及删除。

 

先记录多个字段为依据，删除多个字段同时相同的记录

SELECT t.a, t.b, t.c, max(rowid) FROM temp t

GROUP BY t.a, t.b, t.c

HAVING COUNT(*)>1

 

--保留rowid最大的、重复的数据

（方法一：）DELETE FROM temp WHERE rowid not in (

SELECT max(rowid) FROM temp t

GROUP BY t.a, t.b, t.c

HAVING COUNT(*)>1

)

 

--但是上面的方法一般效率会比较低，可以通过手动的每次删除最大rowid的记录

--这种办法对重复次数不多的表比较好，如果某些记录重复几十次就比较麻烦。

（方法二：）DELETE FROM temp WHERE rowid in (

SELECT max(rowid) FROM temp t

GROUP BY t.a, t.b, t.c

HAVING COUNT(*)>1

)

 

--方法二的缺点是如果temp表一旦数据量过大，比如几十万，上百上千万，那么这条语句执行起来基本是等不起的。

办法三：

将下面的结果放到临时表，然后用游标读取每个rowid，在去表temp表中删除对于的rowid

CREATE TABLE aaa AS

SELECT max(rowid) dataid FROM temp t

GROUP BY t.a, t.b, t.c

HAVING COUNT(*)>1

 

DECLARE

CURSOR my_cursor IS SELECT dataid FROM aaa;

rid aaa%rowtype;

BEGIN

    OPEN my_cursor;

    LOOP

    FETCH my_cursor INTO rid;

    EXIT WHEN my_cursor%NOTFOUND;

   DELETE FROM temp WHERE rowid=rid;

   END LOOP;

END;

 

这样效率能够大大的加快。但是这样做只是每次都删除重复记录中最大rowid的记录。

可以考虑在建临时表的时候这样：

SELECT rowid FROM temp WHERE

rowid IN (SELECT rowid FROM TEMP

GROUP BY a, b, c

HAVING COUNT(*)>1

)

AND NOT IN (SELECT MAX(rowid) FROM TEMP

GROUP BY a, b, c

HAVING COUNT(*)>1

)

 

 

如果是针对不同数据库的数据迁移，删除重复数据，最好是不要采取DBLINK 这种方式，这种方式大大的依赖网络资源，可以需要迁移的数据涉及的表都导出，然后在本地数据库建立临时表，将数据导入。这样就可以在本地数据库中查询出需要迁移的数据，直接转移的相应的表。

 

或者是不导出远程数据库服务器上的表，而是在那上面执行查询，导出执行结果。再放入本地库，将查询结果插入到相应表。