萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 表重複記錄查找與清理三大法

建立唯一索引遇到 ORA-01452: cannot create unique index; duplicate keys found,必須先清理重複資料。以 code_ref 表為例,三種定位重複列的方法:

方法一:ROWID 自連接 (效能最佳)

-- 保留 ROWID 最大者 (最新插入),刪除其餘
DELETE FROM code_ref a
WHERE rowid > (
    SELECT MIN(rowid)
    FROM code_ref b
    WHERE a.key_col1 = b.key_col1
      AND a.key_col2 = b.key_col2
);

ROWID 為物理地址唯一值,MIN/MAX 可直接定位重複群組內的保留/刪除目標,無需排序。

方法二:GROUP BY / HAVING (直觀易懂)

-- 查找重複鍵值
SELECT key_col1, key_col2, COUNT(*)
FROM code_ref
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1;

-- 配合刪除 (需子查詢)
DELETE FROM code_ref
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM code_ref
    GROUP BY key_col1, key_col2
);

方法三:EXCEPTIONS INTO 子句 (舊版相容)

  1. 建立例外表:@?/rdbms/admin/utlexcpt.sql (建立 EXCEPTIONS 表)
  2. 嘗試啟用約束並導向例外表:
    ALTER TABLE code_ref ADD CONSTRAINT uk_code_ref UNIQUE (key_col1, key_col2)
    EXCEPTIONS INTO EXCEPTIONS;
  3. 關聯查詢重複列:
    SELECT c.*
    FROM code_ref c, EXCEPTIONS e
    WHERE c.rowid = e.row_id
      AND e.constraint = 'UK_CODE_REF';

效能與選擇建議

方法適用場景優點缺點
ROWID 自連接大表、頻繁清理單掃描、無排序、效能最好SQL 較抽象
GROUP BY中小表、一次性清理語義清晰、易維護大表可能產生大量排序/雜湊
EXCEPTIONS需記錄違規細節、舊版相容可捕獲約束名稱、錯誤類型需額外建表、兩階段操作

清理後務必重新收集統計資訊 (DBMS_STATS.GATHER_TABLE_STATS) 確保優化器擁有正確資訊。

copyright © 萬盛學電腦網 all rights reserved