Oracle 查詢與刪除重複資料 SQL 完整範本：單欄位、多欄位、保留 ROWID 最小值

重複資料清理是資料治理常態作業。本文提供標準 SQL 範本，涵蓋單欄位/多欄位重複判定、查詢重複列、刪除重複僅保留 ROWID 最小（最早插入）的一筆，並解釋 ROWID 特性。

核心概念

• ROWID：Oracle 物理定位碼，唯一識別每一列，可直接用於最快速的單列存取。
• 重複定義：依業務鍵（單欄或多欄組合）分組，COUNT(*) > 1 即為重複群組。
• 保留策略：保留 MIN(ROWID)（最早寫入），刪除其餘。

單欄位重複（以 peopleId 為例）

1. 查詢重複列

SELECT *
FROM people
WHERE peopleId IN (
  SELECT peopleId FROM people
  GROUP BY peopleId
  HAVING COUNT(peopleId) > 1
);

2. 刪除重複（保留 ROWID 最小）

DELETE FROM people
WHERE peopleId IN (
  SELECT peopleId FROM people
  GROUP BY peopleId
  HAVING COUNT(peopleId) > 1
)
AND ROWID NOT IN (
  SELECT MIN(ROWID) FROM people
  GROUP BY peopleId
  HAVING COUNT(peopleId) > 1
);

多欄位重複（以 peopleId + seq 為例）

3. 查詢重複列

SELECT *
FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq
  HAVING COUNT(*) > 1
);

4. 刪除重複（保留 ROWID 最小）

DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq
  HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
  SELECT MIN(ROWID) FROM vitae
  GROUP BY peopleId, seq
  HAVING COUNT(*) > 1
);

5. 查詢重複但不含保留列（供人工覆核）

SELECT *
FROM vitae a
WHERE (a.peopleId, a.seq) IN (
  SELECT peopleId, seq FROM vitae
  GROUP BY peopleId, seq
  HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
  SELECT MIN(ROWID) FROM vitae
  GROUP BY peopleId, seq
  HAVING COUNT(*) > 1
);

進階：找出所有有重複值的欄位組合

-- 單欄位
SELECT name, COUNT(*) FROM A GROUP BY name HAVING COUNT(*) > 1;

-- 多欄位（名稱+性別）
SELECT name, sex, COUNT(*) FROM A GROUP BY name, sex HAVING COUNT(*) > 1;

大表效能優化版（使用 ROW_NUMBER，11g+）

DELETE FROM big_table
WHERE ROWID IN (
  SELECT row_id FROM (
    SELECT ROWID AS row_id,
           ROW_NUMBER() OVER (PARTITION BY biz_key ORDER BY insert_dt) AS rn
    FROM big_table
  )
  WHERE rn > 1
);

• 單次全表掃描，無巢狀子查詢，效能優於 IN 子查詢寫法。
• PARTITION BY 對應業務鍵，ORDER BY 決定保留哪一筆。

注意事項

• 刪除前必先備份或建立測試表驗證。
• 若表有外鍵參考，需先處理子表資料或暫停約束。
• 含 LOB 欄位的表，ROWID 刪除可能產生大量 UNDO，分批提交（COMMIT 每 5 萬筆）。
• 分區表刪除重複：在分區層級操作或配合 EXCHANGE PARTITION 方案（見「大表刪除重複資料高效策略」一文）。