删除重复数据的一种高效的方法

表demo是重复拷贝自dba_objects，有88万左右，不重复的是27323，没有索引
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗时：几个小时以上

方法二： delete from demo where rowid in
(select rid from
(select rowid rid,row_number() over(partition by object_id order by rowid) rn
from demo)
where rn <> 1 );
耗时：30秒

方法三： create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;
共耗时： 10秒，适合大数据量的情况，产生更少回滚量；

评论

2 楼 czllfy 2007-03-16  

没有主键（Primary Key）约束保护的表格可能会让重复的数据行被插入进来。查找这种重复数据的传统方式是通过GROUP BY和HAVING关键字进行查询。在根据关键列把数据分组并计算每个组里的行数之后，有一个以上成员的组就是带有重复数据的组。

尽管发现这样的数据行很容易，但是解决这一问题却十分耗时。在Oracle里，独特的ROWID伪列（pseudocolumn）意味着没有两个列是真正一模一样的。你可以总是利用删除（DELETE）查询来参考一个以外的所有ROWID，以便删除所有的重复数据。这非常有效——如果你没有太多的重复数据需要删除的话。而Oracle 9i里引入的分析函数给予了我们一种更简单的方式来进行这种清除工作。

ROW_NUMBER()分析函数与ROWNUM伪列相似的地方在于它们都能够给输出的行编号。但是ROWNUM给出的是整个数据列完整的序列，而ROW_NUMBER会在我们在数据列里定义的每个分区里把编号重新设置回1。这样做的结果是不仅能够很容易就看到哪个组里有多个成员，还能够确切知道需要删除哪个行。

分析查询的格式是：

Functionname (arguments) OVER (PARTITION BY columns ORDER BY columns)
现在让我们假设在创建SCOTT.EMP表格副本的时候出现了错误，所有的行都被输入了两遍。尝试加入一个主键约束会失败，因为数据已经出现了重复。列表A显示了这一过程，为了清楚说明问题，它被分成两个阶段：

Listing A SQL> -- Will you just LOOK at this table?  Lots of duplicates!
SQL>
SQL> SELECT empno, ename
  2  FROM emp2
  3  ORDER BY empno;

     EMPNO ENAME                                                               
---------- ----------                                                          
      7369 SMITH                                                               
      7369 SMITH                                                               
      7499 ALLEN                                                               
      7499 ALLEN                                                               
      7521 WARD                                                                
      7521 WARD                                                                
      7566 JONES                                                               
      7566 JONES                                                               
      7654 MARTIN                                                              
      7654 MARTIN                                                              
      7698 BLAKE                                                               
      7698 BLAKE                                                               
      7782 CLARK                                                               
      7782 CLARK                                                               
      7788 SCOTT                                                               
      7788 SCOTT                                                               
      7839 KING                                                                
      7839 KING                                                                
      7844 TURNER                                                              
      7844 TURNER                                                              
      7876 ADAMS                                                               
      7876 ADAMS                                                               
      7900 JAMES                                                               
      7900 JAMES                                                               
      7902 FORD                                                                
      7902 FORD                                                                
      7934 MILLER                                                              
      7934 MILLER                                                              

28 rows selected.

SQL> -- First step: number the duplicates of each empno
SQL>
SQL> SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
  2  FROM emp2;

ROWID                      RN                                                  
------------------ ----------                                                  
AAAM1UAAEAAAAGsAAA          1                                                  
AAAM1UAAEAAAAGuAAA          2                                                  
AAAM1UAAEAAAAGuAAB          1                                                  
AAAM1UAAEAAAAGsAAB          2                                                  
AAAM1UAAEAAAAGsAAC          1                                                  
AAAM1UAAEAAAAGuAAC          2                                                  
AAAM1UAAEAAAAGuAAD          1                                                  
AAAM1UAAEAAAAGsAAD          2                                                  
AAAM1UAAEAAAAGsAAE          1                                                  
AAAM1UAAEAAAAGuAAE          2                                                  
AAAM1UAAEAAAAGsAAF          1                                                  
AAAM1UAAEAAAAGuAAF          2                                                  
AAAM1UAAEAAAAGsAAG          1                                                  
AAAM1UAAEAAAAGuAAG          2                                                  
AAAM1UAAEAAAAGsAAH          1                                                  
AAAM1UAAEAAAAGuAAH          2                                                  
AAAM1UAAEAAAAGsAAI          1                                                  
AAAM1UAAEAAAAGuAAI          2                                                  
AAAM1UAAEAAAAGsAAJ          1                                                  
AAAM1UAAEAAAAGuAAJ          2                                                  
AAAM1UAAEAAAAGsAAK          1                                                  
AAAM1UAAEAAAAGuAAK          2                                                  
AAAM1UAAEAAAAGsAAL          1                                                  
AAAM1UAAEAAAAGuAAL          2                                                  
AAAM1UAAEAAAAGsAAM          1                                                  
AAAM1UAAEAAAAGuAAM          2                                                  
AAAM1UAAEAAAAGuAAN          1                                                  
AAAM1UAAEAAAAGsAAN          2                                                  

28 rows selected.

SQL> -- Now, use that as an inline view, and select just the dups
SQL> -- We're including the row number, it won't be in the final query
SQL>
SQL> SELECT ROWID, rn
  2  FROM
  3    (SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
  4     FROM emp2)
  5  WHERE rn > 1;

ROWID                      RN                                                  
------------------ ----------                                                  
AAAM1UAAEAAAAGuAAA          2                                                  
AAAM1UAAEAAAAGsAAB          2                                                  
AAAM1UAAEAAAAGuAAC          2                                                  
AAAM1UAAEAAAAGsAAD          2                                                  
AAAM1UAAEAAAAGuAAE          2                                                  
AAAM1UAAEAAAAGuAAF          2                                                  
AAAM1UAAEAAAAGuAAG          2                                                  
AAAM1UAAEAAAAGuAAH          2                                                  
AAAM1UAAEAAAAGuAAI          2                                                  
AAAM1UAAEAAAAGuAAJ          2                                                  
AAAM1UAAEAAAAGuAAK          2                                                  
AAAM1UAAEAAAAGuAAL          2                                                  
AAAM1UAAEAAAAGuAAM          2                                                  
AAAM1UAAEAAAAGsAAN          2                                                  

14 rows selected.

SQL> -- Now we DELETE all the rows in that set
SQL>
SQL> DELETE FROM emp2
  2  WHERE ROWID IN
  3    (SELECT ROWID
  4     FROM (SELECT ROWID,
  5              ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
  6           FROM emp2)
  7     WHERE rn > 1);

14 rows deleted.

SQL> commit;

Commit complete.

SQL> -- Show the de-dup'ed table
SQL>
SQL> SELECT empno, ename
  2  FROM emp2;

     EMPNO ENAME                                                               
---------- ----------                                                          
      7369 SMITH                                                               
      7521 WARD                                                                
      7654 MARTIN                                                              
      7698 BLAKE                                                               
      7782 CLARK                                                               
      7788 SCOTT                                                               
      7839 KING                                                                
      7844 TURNER                                                              
      7876 ADAMS                                                               
      7900 JAMES                                                               
      7902 FORD                                                                
      7499 ALLEN                                                               
      7566 JONES                                                               
      7934 MILLER                                                              

首先是一个分析查询，通过empno行来分区；它使用ROW_NUMBER()给每个分区进行编号。如果没有重复的内容，分区就只有一个行，编号是“1”。但是，如果存在重复，那么它们就会被编上2、3等号码。这个查询还会返回我们用来唯一识别数据行的ROWID。第一个查询然后就被用作另外一个查询的内联视图，这第二个查询使用一个WHERE子句过滤掉“1”行，只返回重复的内容。最后，一个DELETE语句通过第二个查询使用IN操作符来删掉所有的重复内容。

就和所有的大规模DELETE一样，你需要记住的是，最好把想要保留的行（也就是说那些ROW_NUMBER为1的行）保存到一个新的表格里。INSERT所造成的负载要比DELETE小得多。

1 楼 yiding_he 2007-03-15  

5555  好想了解怎么回事，不过看不懂，兄台能否讲解一下？