pengpenglin
人,必须选择一种生活方式并有勇气坚持下去
BlogJava
首页
新随笔
新文章
联系
聚合
管理
posts - 262, comments - 221, trackbacks - 0
【原】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分组的方式是很快的,但是在海量数据的情况下则反而是方式一最快,因为省去了自连接、排序、分组的时间
-------------------------------------------------------------
生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。
posted on 2008-06-18 11:04
Paul Lin
阅读(1645)
评论(0)
编辑
收藏
所属分类:
Oracle 开发
新用户注册
刷新评论列表
只有注册用户
登录
后才能发表评论。
网站导航:
博客园
IT新闻
知识库
C++博客
博问
管理
相关文章:
【原】Oracle开发专题之:时间运算2(日期截取及四舍五入)
【原】Oracle开发专题之:时间运算
【原】Oracle开发专题之:分析函数总结
【原】Oracle开发专题之:报表函数
【原】Oracle开发专题之:窗口函数
【原】Oracle应用专题之:分析函数3(Top/Bottom N、First/Last、NTile)
【原】Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number)
【原】Oracle开发专题之:分析函数(OVER)
【原】Oracle开发专题之:删除重复记录
【原】Oracle开发专题之:行列转换
<
2008年6月
>
日
一
二
三
四
五
六
25
26
27
28
29
30
31
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
常用链接
我的随笔
我的评论
我的参与
最新评论
留言簿
(21)
给我留言
查看公开留言
查看私人留言
随笔分类
J2EE 框架(9)
J2EE基础(4)
J2SE(43)
Java 工具(5)
Oracle Concept(4)
Oracle SQL/PLSQL(9)
Oracle 开发(13)
Oracle 管理(4)
Oracle 调优
Oracle 错误诊断
RoR(19)
UML(3)
Unix / Linux(13)
Web基础(19)
其它技术(7)
感悟(3)
杂项(7)
架构与性能(8)
模式与重构(19)
灌水(8)
电影与音乐(16)
走过的路(1)
软件过程与软件方法(3)
阳光户外(2)
项目管理(36)
随笔档案
2012年2月 (3)
2011年11月 (4)
2011年10月 (1)
2011年9月 (2)
2011年8月 (2)
2011年7月 (5)
2011年6月 (3)
2011年5月 (1)
2011年4月 (1)
2011年3月 (3)
2011年1月 (1)
2010年12月 (1)
2010年11月 (5)
2010年10月 (3)
2010年9月 (1)
2010年7月 (1)
2010年6月 (1)
2010年5月 (4)
2010年4月 (9)
2010年3月 (19)
2010年2月 (8)
2010年1月 (3)
2009年12月 (34)
2009年11月 (1)
2009年10月 (2)
2009年7月 (4)
2009年6月 (5)
2009年5月 (3)
2009年4月 (2)
2009年3月 (1)
2009年2月 (5)
2009年1月 (5)
2008年12月 (13)
2008年11月 (4)
2008年10月 (1)
2008年9月 (6)
2008年8月 (5)
2008年7月 (3)
2008年6月 (31)
2008年5月 (10)
2008年4月 (9)
2008年3月 (7)
2008年2月 (4)
2008年1月 (19)
BlogJava热点博客
BeanSoft
Jack.Wang
如坐春风
诗特林
郑晖
银河使者
阿密果
隔叶黄莺
好友博客
无羽苍鹰
搜索
最新评论
1. re: 【Java基础专题】编码与乱码(01)---编码基础[未登录]
666666666666666666666这几天正在做个类似工程编码出现错误
--李
2. re: 【Java基础专题】IO与文件读写---使用Apache commons IO简化文件读写
不错
--阿斯兰
3. re: 【Java基础专题】编码与乱码(03)----String的toCharArray()方法
多谢分享
--thx
4. re: 【Java基础专题】编码与乱码(05)---GBK与UTF-8之间的转换
评论内容较长,点击标题查看
--karl
5. re: 【Java基础专题】编码与乱码(01)---编码基础[未登录]
谢谢,帮了我大忙!
--小龙
阅读排行榜
1. Oracle中如何插入特殊字符:& 和 ' (多种解决方案)(48343)
2. getOutputStream() has already been called for this response的解决方法(43320)
3. 【Java基础专题】编码与乱码(05)---GBK与UTF-8之间的转换(36943)
4. 360安全卫士误删audiosrv.dll文件造成系统无声的解决方案(21210)
5. Java中读取字节流并按指定编码转换成字符串的方法(17299)
评论排行榜
1. 360安全卫士误删audiosrv.dll文件造成系统无声的解决方案(33)
2. 【原】Oracle开发专题之:时间运算(11)
3. 【Java基础专题】编码与乱码(05)---GBK与UTF-8之间的转换(11)
4. getOutputStream() has already been called for this response的解决方法(10)
5. 【原】Oracle开发专题之:分析函数(OVER)(8)