Oracle 資料表重複記錄查找與刪除三大經典方法

背景

嘗試建立唯一索引時遇到 ORA-01452: 找到重複關鍵字，必須先清理重複資料。以下以 code_ref 表為例示範三種方法。

方法一：自關聯查詢（利用 ROWID）

Oracle 每列擁有唯一 ROWID，配合 MAX/MIN 可快速定位重複行。

SELECT a.rowid, a.*
FROM code_ref a
WHERE a.rowid > (
  SELECT MIN(b.rowid)
  FROM code_ref b
  WHERE a.col1 = b.col1
    AND a.col2 = b.col2
);

保留 MIN(rowid) 的行，其餘為重複。

方法二：GROUP BY / HAVING 分組統計

按唯一索引欄位分組，統計筆數 > 1 即為重複。

SELECT col1, col2, COUNT(*)
FROM code_ref
GROUP BY col1, col2
HAVING COUNT(*) > 1;

刪除時可結合 IN 子查詢或建立暫存表提升效能。

方法三：EXCEPTIONS INTO 子句

1. 執行 @$ORACLE_HOME/rdbms/admin/utlexcpt.sql 建立 EXCEPTIONS 表（NT 路徑 ORACLE_HOME\Ora81\rdbms\admin，Unix 為 $ORACLE_HOME/rdbms/admin）。
2. 嘗試啟用唯一約束並導入異常：

   ALTER TABLE code_ref ADD CONSTRAINT uq_code_ref UNIQUE (col1, col2)
   EXCEPTIONS INTO exceptions;

3. 查詢異常表並關聯原表取得重複行：

   SELECT c.*
   FROM code_ref c, exceptions e
   WHERE c.rowid = e.row_id
     AND e.constraint = 'UQ_CODE_REF';

效能建議

• 大資料量時，優先使用 暫存表 存放重複鍵值/ROWID，再執行刪除，避免重複掃描。
• 若需保留最新記錄，改用 MAX(rowid) 保留策略。
• 刪除前務必備份，確認無誤後提交。