Oracle 查找重複記錄：GROUP BY + HAVING 經典模式與性能優化

業務常需「找出主鍵/唯一鍵重複的髒數據」。本文給出標準 SQL、大表優化版、刪除重複留一條的完整方案。

一、標準查重 SQL

-- 假設 person 表：id (PK), name
-- 找出 id 重複的記錄
SELECT id, COUNT(*) AS dup_cnt
FROM person
GROUP BY id
HAVING COUNT(*) > 1;

二、大表優化：並行 + 索引掃描

SELECT /*+ PARALLEL(p, 8) FULL(p) */ id, COUNT(*)
FROM person p
GROUP BY id
HAVING COUNT(*) > 1;

• 無索引時 FULL(p) 強制全表掃描避免索引快速全掃描（Index Fast Full Scan）的隨機 I/O。
• PARALLEL 利用多 CPU 核心加速聚合。
• 若有唯一索引，優化器自動選 INDEX FAST FULL SCAN，無需 Hint。

三、刪除重複保留一條（ROWID 法）

DELETE FROM person
WHERE rowid NOT IN (
  SELECT MIN(rowid)
  FROM person
  GROUP BY id
);

• MIN(rowid) 保留最早插入的那行（ROWID 編碼包含文件/塊/槽號，天然有序）。
• 大表分批刪除：
  DELETE FROM person WHERE rowid IN (SELECT rowid FROM (SELECT rowid FROM person WHERE id IN (SELECT id FROM person GROUP BY id HAVING COUNT(*) > 1) AND ROWNUM <= 10000)); 循環提交。

四、複合鍵查重

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

五、生產級查重報表腳本

SET LINES 200 PAGES 1000
COL owner FOR A20
COL table_name FOR A30
COL dup_keys FOR A100
COL dup_cnt FOR 999,999,999

SELECT dt.owner,
       dt.table_name,
       LISTAGG(dtc.column_name, ', ') WITHIN GROUP (ORDER BY dtc.position) AS dup_keys,
       MAX(dtc.dup_cnt) AS dup_cnt
FROM (
  SELECT owner, table_name, column_name, position, COUNT(*) AS dup_cnt
  FROM dba_tab_columns
  WHERE owner = UPPER('&SCHEMA')
  GROUP BY owner, table_name, column_name, position
  HAVING COUNT(*) > 1
) dtc
JOIN dba_tables dt ON dt.owner = dtc.owner AND dt.table_name = dtc.table_name
GROUP BY dt.owner, dt.table_name
ORDER BY dup_cnt DESC;

將查重納入「數據質量巡檢」每日跑批，異常即時推送至數據治理平台，從源頭阻斷髒數據入庫。