这里我来给出两种查询和删除重复记录的方法,一种是使用rowid辅助完成的,另外一种是借助分析函数的力量来完成的。
这两种方法的执行效率相对其他方法是高效的。即便如此,这两种方法之间也有着本质上的性能区别,我将通过实验的方式给大家展示一下这两种方法,并道出其中的本质差别。
1.创建实验用表并初始化几条样本数据
sec@ora10g> create table t (x number, y varchar2(10));
sec@ora10g> insert into t values (1, 'sec');
sec@ora10g> insert into t values (2, 'Andy01');
sec@ora10g> insert into t values (2, 'Andy02');
sec@ora10g> insert into t values (3, 'Anna');
sec@ora10g> insert into t values (4, 'Anna');
sec@ora10g> insert into t values (5, 'John');
sec@ora10g> commit;
sec@secooler> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed.
sec@ora10g> select * from t;
X Y
---------- --------------------
1 sec
2 Andy01
2 Andy02
3 Anna
4 Anna
5 John
6 rows selected.
2.第一种使用rowid辅助查询和删除重复记录的方法
1)查询重复记录
sec@ora10g> SELECT *
2 FROM t t1
3 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
4 FROM t t2
5 WHERE t1.x = t2.x)
6 /
X Y
---------- --------------------
2 Andy02
BTW:如果想要查询x和y字段同时重复的内容,可以在上面的子查询中再添加一个“AND t1.y = t2.y”条件即可。
2)删除重复记录
可以简单的将上面的查询语句改写成删除语句便可完成删除任务。
sec@ora10g> DELETE FROM t t1
2 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
3 FROM t t2
4 WHERE t1.x = t2.x)
5 /
1 row deleted.
可以看到,此时x字段重复的内容已经被删除了。
sec@ora10g> select * from t;
X Y
---------- --------------------
1 sec
2 Andy01
3 Anna
4 Anna
5 John
3.第二种使用分析函数辅助查询和删除重复记录的方法
1)使用分析函数可以快速的定位重复记录的位置,下面结果中rn值大于1的行即表示重复行。
sec@ora10g> SELECT t1.x,
2 t1.y,
3 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
4 FROM t t1
5 /
X Y RN
---------- -------------------- ----------
1 sec 1
2 Andy01 1
2 Andy02 2
3 Anna 1
4 Anna 1
5 John 1
6 rows selected.
2)进一步使用上面的rn结果作为辅助条件便可得到重复记录内容
sec@ora10g> SELECT t2.x, t2.y
2 FROM (SELECT t1.x,
3 t1.y,
4 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
5 FROM t t1) t2
6 WHERE t2.rn > 1
7 /
X Y
---------- --------------------
2 Andy02
3)删除方法
(1)第一种方法是利用rowid构造delete语句来完成删除,这种方法效率较低。
sec@ora10g> DELETE FROM t WHERE ROWID IN (
2 SELECT rowid
3 FROM (SELECT t1.x,
4 t1.y,
5 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
6 FROM t t1) t2
7 WHERE t2.rn > 1
8 )
9 /
1 row deleted.
(2)第二种方法,可以使用构造中间表t1的方法来完成,这是一种非常高效的去重方法,推荐在具有海量数据的数据库环境中使用。
sec@ora10g> create table t1 as
2 SELECT t2.x, t2.y
3 FROM (SELECT t1.x,
4 t1.y,
5 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
6 FROM t t1) t2
7 WHERE t2.rn = 1
8 /
Table created.
sec@ora10g> drop table t;
Table dropped.
sec@ora10g> alter table t1 rename to t;
Table altered.
sec@ora10g> select * from t;
X Y
---------- --------------------
1 sec
2 Andy01
3 Anna
4 Anna
5 John
4.比较两种查询方法的执行计划,便可得到两种方法内在的性能差距的出处。
1)第一种使用rowid辅助查询的执行计划如下
sec@ora10g> set autot trace exp
sec@ora10g> SELECT *
2 FROM t t1
3 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
4 FROM t t2
5 WHERE t1.x = t2.x)
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3924487551
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 55 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 6 | 66 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 11 | | |
|* 4 | TABLE ACCESS FULL| T | 1 | 11 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1".ROWID<> (SELECT MIN("T2".ROWID) FROM "T" "T2" WHERE
"T2"."X"=:B1))
4 - filter("T2"."X"=:B1)
2)第二种使用分析函数辅助查询的执行计划如下
sec@ora10g> SELECT t1.x,
2 t1.y,
3 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
4 FROM t t1
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2335850315
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 66 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 6 | 66 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 6 | 66 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
3)通过比较上面两个执行计划可以得到如下结论
第一种方法采用2次TABLE ACCESS FULL,第二种方法采用仅一次TABLE ACCESS FULL。
从执行计划上可以得出使用分析函数的方法更加的高效。
5.小结
在DBA数据库维护工作中,重复记录去除问题往往是不可避免的。在具有海量数据的数据库中去除重复记录是一件很艰巨的任务,如果方法选择不正确,很可能难以完成任务。
我这里介绍的两种方法都是相对比较高效的,细节之处请慢慢体会。
完成任务的手段和方法很多,只有将维护时间和对生产数据库的冲击较少到最低的方法才是可以接受的正确方法。
分享到:
相关推荐
6.5.1 利用排序去除重复 6.5.2 利用排序进行分组和聚集 6.5.3 基于排序的并算法 6.5.4 基于排序的交和差算法 6.5.5 基于排序的一个简单的连接算法 6.5.6 简单排序连接的分析 6.5.7 一种更有效的基于排序...
Oracle中权限主要分为两种,系统权限和实体权限。 系统权限:系统规定用户使用数据库的权限。(系统权限是对用户而言)。 DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。 RESOURCE:...
由于用户的需求不同,有的是要对学生的信息做个别的改动,有的是需要删除该条记录,所以该窗体同时包括了修改记录的功能和删除记录的功能。又考虑到用户的误操作,为了方便,我在该窗体中增添了取消修改的功能,可一...
§10.13.7 优化步骤和方法 125 §10.13.8 使用ORACLE 诊断工具 126 第三部分 ORACLE应用系统开发优化 128 第11章 诊断与调整工具- 128 §11.1 警告日志文件 128 §11.1.1 警告日志文件管理 128 §11.1.2 参考警告...
动态调用对象的属性和方法——性能和灵活性兼备的方法 消除由try/catch语句带来的warning 微软的应试题完整版(附答案) 一个时间转换的问题,顺便谈谈搜索技巧 .net中的正则表达式使用高级技巧 (一) C#静态成员和...
6.2 vim编辑器的两种工作模式 135 6.2.1 输入模式 135 6.2.2 命令模式 135 6.3 保存编辑的文件并退出vim 136 6.4 vim编辑器的基本命令 137 6.4.1 移动光标位置 137 6.4.2 输入文本 138 6.4.3 修改与替换文本 138 ...
6.2 vim编辑器的两种工作模式 135 6.2.1 输入模式 135 6.2.2 命令模式 135 6.3 保存编辑的文件并退出vim 136 6.4 vim编辑器的基本命令 137 6.4.1 移动光标位置 137 6.4.2 输入文本 138 6.4.3 修改与替换文本 138 ...
螺旋模型是一种引入了风险分析与规避机制的过程模型,是瀑布模型、快速原型方法和风险分析方法的有机结合。其基本方法是,在各个阶段创建原型进行项目试验,以降低各个阶段可能遇到的项目风险。 6.喷泉模型 喷泉...
在下列两种情况下也只能采用顺序查找: ①如果线性表为无序表,则不管是顺序存储结构还是链式存储结构,只能用顺序查找; ②即使是有序线性表,如果采用链式存储结构,也只能用顺序查找。 1.7.2 二分法查找 二分法...
10.求以下表达式的值,写出您想到的一种或几种实现方法: 1-2+3-4+……+m [Page] 答: int Num = this.TextBox1.Text.ToString() ; int Sum = 0 ; for (int i = 0 ; i ; i++) { if((i%2) == 1) { Sum += i ; ...
6.2 vim编辑器的两种工作模式 135 6.2.1 输入模式 135 6.2.2 命令模式 135 6.3 保存编辑的文件并退出vim 136 6.4 vim编辑器的基本命令 137 6.4.1 移动光标位置 137 6.4.2 输入文本 138 6.4.3 修改与替换文本 138 ...
CCAMS系统是一种用于局域网下的CS模式的软件管理和监测系统源码 它包括客户端和服务端,客户端软件主要作用是监测本主机的活动,并将监测到的信息定时发送给服务器。服务器可以将收集到的信息以柱状图和文件列表以及...
CCAMS系统是一种用于局域网下的CS模式的软件管理和监测系统源码 它包括客户端和服务端,客户端软件主要作用是监测本主机的活动,并将监测到的信息定时发送给服务器。服务器可以将收集到的信息以柱状图和文件列表以及...
CCAMS系统是一种用于局域网下的CS模式的软件管理和监测系统源码 它包括客户端和服务端,客户端软件主要作用是监测本主机的活动,并将监测到的信息定时发送给服务器。服务器可以将收集到的信息以柱状图和文件列表以及...
CCAMS系统是一种用于局域网下的CS模式的软件管理和监测系统源码 它包括客户端和服务端,客户端软件主要作用是监测本主机的活动,并将监测到的信息定时发送给服务器。服务器可以将收集到的信息以柱状图和文件列表以及...
CCAMS系统是一种用于局域网下的CS模式的软件管理和监测系统源码 它包括客户端和服务端,客户端软件主要作用是监测本主机的活动,并将监测到的信息定时发送给服务器。服务器可以将收集到的信息以柱状图和文件列表以及...
85 <br>0130 复制字符串中指定的字符 85 <br>0131 巧截字符串的数字 86 <br>0132 如何存储变长字符串 86 <br>0133 在进行字符串比较时忽略大小写 87 <br>0134 如何去除字符串尾空格 87 ...