[转载]Oracle开发专题之：删除重复记录
原文地址：Oracle开发专题之：删除重复记录作者：☆水『若寒
一、测试环境：
假设目前我们有一个表：test，该表的结构如下：
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID
NUMBER
SEQ NUMBER
现在我们向表中插入200W条数据，这200W条数据中有一半是重复的。
create or replace procedure gen_duplicated_records as
i number;
j number;
begin
for i in 1 .. 2 loop
for j in 1 .. 1000000 loop
insert into test values (j, j + 10);
end loop;
commit;
end loop;
end;
我们的最终目的就是剔除这一半的重复记录。下面来看一下各种方法的使用及效率区别
二、使用临时表进行删除：
这个是最简单的思路了，创建一张临时表，将原表中的数据拷贝一半过去，再查询出来。
SQL> set timing on;
SQL>
SQL> create table test_2 as select distinct * from test;
Table created.
Elapsed: 00:00:07.09
SQL>
该方法耗时7.09秒，测试数据库位于服务器上。考虑到服务器和本机位于同一个局域网内，该时间如果在真正的生产环境中应该至上延长1倍以上。
三、使用rowid进行删除：
我们知道在Oracle中，rowid是用来唯一表示一条记录的伪列，任意两条记录的rowid都是不同的，即便内容看起来一模一样。所以我们的思路是：使用表的自连接，查找那些内容相同但rowid不同的记录，即为重复记录。然后随意选择其中一个rowid代表的记录，删除另一条记录。
我们来看一下其中id=1的记录在自连接后的情况：
SQL> select a.*, a.rowid, b.*, b.rowid from test a, test b where a.id = b.id and a.seq = b.seq and a
.id = 1;
ID SEQ ROWID ID SEQ ROWID
---------- ---------- ------------------ ---------- ---------- ------------------
1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAAKAAA
1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAAKAAA
1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAgQAGX
1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAgQAGX
Elapsed: 00:00:02.08
SQL>
我们看到自连接后的4条记录中有2条的rowid是不同的，说明这2条记录就是重复记录，所以我们可以通过选择其中rowid较大或较小的记录，来删除剩余的记录。但是这种方法的一个很大的缺点就是由于采用了“自连接”，对于像我这样的测试表中有200W条记录的情况，其自连接后的记录数是一个天文数字(其实本人的测试就因为等待过久而不得不取消)。
我们换另外一种方法：
DELETE FROM test t1
WHERE t1.ROWID NOT IN (
SELECT MAX(t2.rowid)
FROM test t2
WHERE t1.id = t2.id AND t1.seq = t2.seq);
实践证明，这种方法对大量数据的情况，效率依然是很低的。结果如同上一种方法。假如我们再结合group by呢？
SQL> DELETE FROM test
2 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM test GROUP BY id, seq);
效果如同前面两个方法一样，大量的连接、排序、分组让依靠rowid来删除重复记录变得很耗时，反而是采用方法1的情况下速度很快(本人测试了2次，都是连接测试服务器进行测试，第一次用时7.09秒，第二次用时14.656秒)。
小结：
在数据量不大的情况下，采用根据rowid或结合group by分组的方式是很快的，但是在海量数据的情况下则反而是方式一最快，因为省去了自连接、排序、分组的时间
假设目前我们有一个表：test，该表的结构如下：
SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- ID
NUMBER SEQ NUMBER现在我们向表中插入200W条数据，这200W条数据中有一半是重复的。
create or replace procedure gen_duplicated_records as i number; j number;begin for i in 1 .. 2 loop for j in 1 .. 1000000 loop insert into test values (j, j + 10); end loop; commit; end loop;end;我们的最终目的就是剔除这一半的重复记录。下面来看一下各种方法的使用及效率区别
二、使用临时表进行删除：
这个是最简单的思路了，创建一张临时表，将原表中的数据拷贝一半过去，再查询出来。
SQL> set timing on;SQL> SQL> create table test_2 as select distinct * from test;Table created.Elapsed: 00:00:07.09SQL> 该方法耗时7.09秒，测试数据库位于服务器上。考虑到服务器和本机位于同一个局域网内，该时间如果在真正的生产环境中应该至上延长1倍以上。
三、使用rowid进行删除：
我们知道在Oracle中，rowid是用来唯一表示一条记录的伪列，任意两条记录的rowid都是不同的，即便内容看起来一模一样。所以我们的思路是：使用表的自连接，查找那些内容相同但rowid不同的记录，即为重复记录。然后随意选择其中一个rowid代表的记录，删除另一条记录。
我们来看一下其中id=1的记录在自连接后的情况：
SQL> select a.*, a.rowid, b.*, b.rowid from test a, test b where a.id = b.id and a.seq = b.seq and a.id = 1; ID SEQ ROWID ID SEQ ROWID---------- ---------- ------------------ ---------- ---------- ------------------ 1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAAKAAA 1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAgQAGX 1 11 AAAGHIAAJAAAAgQAGXElapsed: 00:00:02.08SQL> 我们看到自连接后的4条记录中有2条的rowid是不同的，说明这2条记录就是重复记录，所以我们可以通过选择其中rowid较大或较小的记录，来删除剩余的记录。但是这种方法的一个很大的缺点就是由于采用了“自连接”，对于像我这样的测试表中有200W条记录的情况，其自连接后的记录数是一个天文数字(其实本人的测试就因为等待过久而不得不取消)。
我们换另外一种方法：
DELETE FROM test t1 WHERE t1.ROWID NOT IN ( SELECT MAX(t2.rowid) FROM test t2 WHERE t1.id = t2.id AND t1.seq = t2.seq);实践证明，这种方法对大量数据的情况，效率依然是很低的。结果如同上一种方法。假如我们再结合group by呢？
SQL> DELETE FROM test 2 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM test GROUP BY id, seq);效果如同前面两个方法一样，大量的连接、排序、分组让依靠rowid来删除重复记录变得很耗时，反而是采用方法1的情况下速度很快(本人测试了2次，都是连接测试服务器进行测试，第一次用时7.09秒，第二次用时14.656秒)。
小结：
在数据量不大的情况下，采用根据rowid或结合group by分组的方式是很快的，但是在海量数据的情况下则反而是方式一最快，因为省去了自连接、排序、分组的时间
