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 /
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
