(面试题)如何查找Oracle数据库中的重复记录

时间:2022-02-22 08:37:32

今天做了个面试题:查找Oracle数据库中的重复记录,下面详细介绍其他方法(参考其他资料)

本文介绍了几种快速查找ORACLE数据库中的重复记录的方法。

下面以表table_name为例,介绍三种不同的方法来确定库表中重复的记录

方法1:利用分组函数查找表中的重复行:按照某个字段分组,找出行数大于1的列,即由重复记录

利用select 语句中的分组函数GROUP BY/HAVING可以很容易确定重复的行。假设需要创建惟一索引的列为column,

对column用group by分组统计并返回每组的个数,如果组中记录数超过1个就存在重复的行。命令如下:
SQL>Select column from table_name
Group by column
Having count(column)>1;
这种查询方式简便、快捷,是ORACLE数据库中最常用的一种方法

方法2:利用伪列自关联查询

在ORACLE数据库的内部,每一表都有一rowid伪列,行标识惟一标识行,提供对特殊行的快速存取。对该列使用最大(max)或者最小(min)函数可以非常容易地确定重复的行。
1)利用max函数查找重复行
SQL>select column1, column2, column3 from table_name a 
          where  rowid< (select max(rowid) from table_name 
              where column1=a.column1 and column2=a.column2 
              and colum3=a.colum3 and ...);

2).利用min函数查找重复行
SQL>select column1,column2,column3 from table_name a 
              where rowid> (select min(rowid) from table_name 
              where column1=a.column1 and column2=a.column2 
              and colum3=a.colum3 and ...);
不过,当表比较大(例如50万条以上)时,这个方法的效率之差令人无法忍受。

方法3:通过定义完整性约束查找重复行

定义一个完整性约束,integrity constraint是一个限制基表中一列或多列值的规则。可通过对表定义UNIQUE约束,指定惟一关键字。为了满足此约束,在惟一关键字列中不能包含相同的值。因此可用EXCEPTIONS INTO子句,将违背激活的完整性约束的记录存储在一个表(EXCEPTIONS)中,此表必须在使用此选项之前先建好。将EXCEPTIONS表和table_name表通过rowid关联起来即可得到表table_name中重复的记录。 具体方法如下:
     1)创建表EXCEPTIONS,用来存放重复记录的信息。
         SQL>create table exceptions(row_id rowid,
                                   owner varchar2(30),
                                   table_name varchar2(30),
                                   constraint varchar2(30));
2)为表table_name定义惟一(UNIQUE)约束,如果在定义的关键字中包含相同的值,系统将提示ORA-02299: 不能创建 - 有重复的值,并将重复记录的信息存入EXCEPTIONS表中。
SQL>alter table table_name
                add constraint unq_column
                unique(column1,column2,……)
              exceptions into EXCEPTIONS;
2. 将表table_name与EXCEPTIONS通过伪列(rowid)建立关联,伪列相等的记录就是table_name中的重复记录。
SQL>select column1,column2,…… 
from table_name a ,EXCEPTIONS b
              where a.rowid=b.row_id ;

这种方式查询效率较高,而且可以较完全的记录下重复记录的信息,但是步骤较繁琐。