一、测试环境:
假设目前我们有一个表:test,该表的结构如下:
SQL
>
desc
test;
Name
Null
? Type
--
--------------------------------------- -------- ----------------------------
ID
NUMBER
SEQ
NUMBER
现在我们向表中插入200W条数据,这200W条数据中有一半是重复的。
create
or
replace
procedure
gen_duplicated_records
as
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
i
number
;
j
number
;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
Table
created.
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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
;
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
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,该表的结构如下:
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
现在我们向表中插入200W条数据,这200W条数据中有一半是重复的。
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
我们的最终目的就是剔除这一半的重复记录。下面来看一下各种方法的使用及效率区别
二、使用临时表进行删除:
这个是最简单的思路了,创建一张临时表,将原表中的数据拷贝一半过去,再查询出来。
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
该方法耗时7.09秒,测试数据库位于服务器上。考虑到服务器和本机位于同一个局域网内,该时间如果在真正的生产环境中应该至上延长1倍以上。
三、使用rowid进行删除:
我们知道在Oracle中,rowid是用来唯一表示一条记录的伪列,任意两条记录的rowid都是不同的,即便内容看起来一模一样。所以我们的思路是:使用表的自连接,查找那些内容相同但rowid不同的记录,即为重复记录。然后随意选择其中一个rowid代表的记录,删除另一条记录。
我们来看一下其中id=1的记录在自连接后的情况:
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
我们看到自连接后的4条记录中有2条的rowid是不同的,说明这2条记录就是重复记录,所以我们可以通过选择其中rowid较大或较小的记录,来删除剩余的记录。但是这种方法的一个很大的缺点就是由于采用了“自连接”,对于像我这样的测试表中有200W条记录的情况,其自连接后的记录数是一个天文数字(其实本人的测试就因为等待过久而不得不取消)。
我们换另外一种方法:
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
实践证明,这种方法对大量数据的情况,效率依然是很低的。结果如同上一种方法。假如我们再结合group by呢?
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
效果如同前面两个方法一样,大量的连接、排序、分组让依靠rowid来删除重复记录变得很耗时,反而是采用方法1的情况下速度很快(本人测试了2次,都是连接测试服务器进行测试,第一次用时7.09秒,第二次用时14.656秒)。
小结:
在数据量不大的情况下,采用根据rowid或结合group by分组的方式是很快的,但是在海量数据的情况下则反而是方式一最快,因为省去了自连接、排序、分组的时间