两种查询和删除重复记录的方法及其性能比较

这里我来给出两种查询和删除重复记录的方法，一种是使用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数据库维护工作中，重复记录去除问题往往是不可避免的。在具有海量数据的数据库中去除重复记录是一件很艰巨的任务，如果方法选择不正确，很可能难以完成任务。
我这里介绍的两种方法都是相对比较高效的，细节之处请慢慢体会。

完成任务的手段和方法很多，只有将维护时间和对生产数据库的冲击较少到最低的方法才是可以接受的正确方法。