【DB笔试面试469】Oracle中如何删除表中重复的记录？

小麦苗DBA宝典

发布于 2019-09-30 16:53:33

2.7K0

发布于 2019-09-30 16:53:33

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目部分

Oracle中如何删除表中重复的记录？

答案部分

平时工作中可能会遇到这种情况，当试图对表中的某一列或几列创建唯一索引时，系统提示ORA-01452 ：不能创建唯一索引，发现重复记录。这个时候只能创建普通索引或者删除重复记录后再创建唯一索引。

重复的数据可能有这样两种情况：第一种是表中只有某些字段一样，第二种是两行记录完全一样。删除重复记录后的结果也分为两种，第一种是重复的记录全部删除，第二种是重复的记录中只保留最新的一条记录，在一般业务中，第二种的情况较多。

1、删除重复记录的方法原理

在Oracle中，每一条记录都有一个ROWID，ROWID在整个数据库中是唯一的，ROWID确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列上的内容都相同，但ROWID不会相同，所以，只要确定出重复记录中那些具有最大ROWID的就可以了，其余全部删除。

2、删除重复记录的方法

若想要删除部分字段重复的数据，则使用下面语句进行删除，下面的语句是删除表中字段1和字段2重复的数据：

DELETE FROM 表名

WHERE (字段1, 字段2) IN (SELECT 字段1,字段2 FROM 表名 GROUP BY 字段1,字段2 HAVING COUNT(1) > 1);

也可以利用临时表的方式，先将查询到的重复的数据插入到一个临时表中，然后进行删除，这样，执行删除的时候就不用再进行一次查询了。如下所示：

CREATE TABLE 临时表 AS (SELECT 字段1,字段2,COUNT(*) FROM 表名 GROUP BY 字段1,字段2 HAVING COUNT(*) > 1);

上面这句话的功能是建立临时表，并将查询到的数据插入其中。有了上面的执行结果，下面就可以进行删除操作了：

DELETE FROM 表名 A WHERE (字段1,字段2) IN (SELECT 字段1，字段2 FROM 临时表);

假如想保留重复数据中最新的一条记录，应该怎么做呢？可以利用ROWID，保留重复数据中ROWID最大的一条记录即可，如下所示：

DELETE FROM TABLE_NAME WHERE ROWID NOT IN (SELECT MAX(ROWID)  FROM TABLE_NAME D GROUP BY D.COL1,D.COL2);

下面给出一个示例：

SYS@raclhr1> CREATE TABLE T_ROWS_LHR_20160809 AS SELECT * FROM SCOTT.EMP;
Table created.
SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;

14 rows created.

SYS@raclhr1> COMMIT;
Commit complete.
SYS@raclhr1> INSERT INTO T_ROWS_LHR_20160809 SELECT * FROM T_ROWS_LHR_20160809;
28 rows created.
SYS@raclhr1> COMMIT;
Commit complete.
SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;
  COUNT(1)
----------
        56
SYS@raclhr1> DELETE FROM T_ROWS_LHR_20160809
  2      WHERE ROWID NOT IN  (SELECT MAX(ROWID)
  3                        FROM T_ROWS_LHR_20160809 D
  4                       group by D.EMPNO,D.ENAME,D.JOB,D.MGR,D.DEPTNO);
42 rows deleted.
SYS@raclhr1> SELECT COUNT(1) FROM T_ROWS_LHR_20160809;
  COUNT(1)
----------
        14
SYS@raclhr1> COMMIT;
Commit complete.

重复数据删除技术可以提供更大的备份容量，实现更长时间的数据保留，还能实现备份数据的持续验证，提高数据恢复服务水平，方便实现数据容灾等。

& 说明：

有关删除重复数据更多的内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2123234/