淺談如何尋找Oracle資料庫中的重複記錄

本文介紹了幾種快速尋找Oracle資料庫中的重複記錄的方法。

作為一個Oracle資料庫開發人員，經常需要對一些表的一列或幾列來建立索引，為行提供直接的快速存取。但在建立時，經常會遇到這樣的提示:

ORA-01452：不能建立惟一索引，發現重複記錄。

Oracle系統提示不能對錶建立一個惟一索引，是因為系統發現表中存在重複的記錄。只有先找到表中的重複記錄並刪除該記錄，才可以建立惟一索引。下面以表table_name為例，介紹三種不同的方法來確定庫表中重複的記錄。

[b]1.[/b][b]利用分組函數尋找表中的重複行[/b][b]
[/b]　　利用select 語句中的分組函數GROUP BY/HAVING可以很容易確定重複的行。假設需要建立惟一索引的列為column,對column用group by分組統計並返回每組的個數，如果組中記錄數超過1個就存在重複的行。命令如下：
SQL>Select column from table_name
Group by column
Having count(column)>1;
這種查詢方式簡便、快捷，是ORACLE資料庫中最常用的一種方法。
[b]2.[/b][b]利用偽列自關聯查詢[/b][b] [/b][b]
[/b]　　在ORACLE資料庫的內部，每一表都有一rowid偽列，行標識惟一標識行,提供對特殊行的快速存取。對該列使用最大(max)或者最小(min)函數可以非常容易地確定重複的行。
1）利用max函數尋找重複行
SQL>select column1, column2, column3 from table_name a
          where rowid
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萬條以上)時,這個方法的效率之差令人無法忍受。
    [b]3.[/b][b]通過定義完整性條件約束尋找重複行[/b]
    定義一個完整性條件約束,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 ;

這種方式查詢效率較高，而且可以較完全的記錄下重複記錄的資訊，但是步驟較繁瑣。