【笔记】查询与删除重复记录

当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。下面以users 表说明怎么查询与删除重复记录：

SQL> desc users;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
ID   NUMBER(4)    Y                        
NAME VARCHAR2(20) Y

SQL> select * from users;

   ID NAME
----- --------------------
   10 天龙八部
    1 笑傲江湖
    2 书剑恩仇录
    3 神雕侠侣
    4 侠客行
    5 倚天屠龙记
    6 碧血剑
    7 鸳鸯刀
    8 飞狐外传
    9 雪山飞狐
    9 雪山飞狐
    8 飞狐外传

12 rows selected

查找重复记录方法：

1、用group by或having

SQL> select * from users group by id, name having count(*)>1;

   ID NAME
----- --------------------
    8 飞狐外传
    9 雪山飞狐

Executed in 0 seconds

2、用max(rowid)、min(rowid)函数

SQL> select * from users a
  2  where a.rowid not in(
  3  select max(b.rowid) from users b
  4  where a.id = b.id
  5  and a.name = b.name);

   ID NAME
----- --------------------
    8 飞狐外传
    9 雪山飞狐

Executed in 0.015 seconds

第一种方法比第二种方法效率要高。

删除重复记录：

1、先将数据导入到新的临时表中，删掉原表中的数据，最后再将临时表中的数据导入到原表中

      SQL> create table users_temp as select distinct * from users;

      SQL> truncate table users;

      SQL> insert into users select * from users_temp;

这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

2、利用max()、min()函数，这里也使用rowid。适用于有少数重复记录，当数据量太大时，效率会变的很低。

      SQL> delete from users a
            2  where a.rowid not in(
            3  select max(rowid) from users b
            4  where a.id = b.id and a.name = b.name)
            5  /

            2 rows deleted

3、使用rowid。在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。

           SQL> delete from users where rowid in
                  2  (select a.ROWID from users a, users b
                  3  where a.rowid > b.rowid and a.id = b.id and a.name = b.name)
                  4  /

4、适用于有大量重复记录的情况(在id，name列上建有索引的时候，用以下语句效率会很高)：

           SQL> delete users where (id, name) in
                2  (select id, name from users group by id, name having count(*)>1)
                3  and rowid not in (select max(rowid) from users group by id, name having count(*)>1);

                3 rows deleted

5、适用于有大量重复记录（Exception  into子句法）

采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些，为了使用“excepeion into ”子句，必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统， Oracle 存放该文件的位置稍有不同，在win2000系统下，该脚本文件存放在$ORACLE_HOME\Ora90\rdbms\admin 目录下；而对于 UNIX 系统，该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下。

具体步骤如下：
SQL>@?/rdbms/admin/utlexcpt.sql

Table created.

SQL> desc exceptions
Name       Type         Nullable Default Comments
---------- ------------ -------- ------- --------
ROW_ID     ROWID        Y                        
OWNER      VARCHAR2(30) Y                        
TABLE_NAME VARCHAR2(30) Y                        
CONSTRAINT VARCHAR2(30) Y 

SQL> alter table users add constraint users_unique unique(id, name) exceptions into exceptions;

alter table users add constraint users_unique unique(id, name) exceptions into exceptions

ORA-02299: 无法验证 (SCOTT.USERS_UNIQUE) - 未找到重复关键字

SQL> create table dups as select * from users where rowid in (select row_id from exceptions);

Table created

SQL> select * from dups;

   ID NAME
----- --------------------
    9 雪山飞狐
   10 天龙八部
   10 天龙八部
   10 天龙八部
    9 雪山飞狐

SQL> select row_id from exceptions;

ROW_ID
------------------
AAAHwaAABAAAMwiAAM
AAAHwaAABAAAMwiAAI
AAAHwaAABAAAMwiAAL
AAAHwaAABAAAMwiAAK
AAAHwaAABAAAMwiAAJ

SQL> delete from users where rowid in (select row_id from exceptions);

5 rows deleted

SQL> insert into users select distinct * from dups;

2 rows inserted

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22786751/viewspace-620170/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22786751/viewspace-620170/