DB2 刪除重複記錄五大場景完整指南：單鍵/組合鍵、保留最小 ROWID

DB2 刪除重複數據的核心思路：識別重複鍵值組 → 定位保留行 (通常取最小 ROWID) → 刪除其餘。本文列舉單字段、多字段五種查詢/刪除模式，代碼可直接生產環境參考執行。

場景 1：單字段 (peopleId) 查詢重複記錄

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

場景 2：單字段 (peopleId) 刪除重複，保留最小 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
);

場景 3：多字段 (peopleId, seq) 查詢重複記錄

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

場景 4：多字段 (peopleId, seq) 刪除重複，保留最小 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：多字段 (peopleId, seq) 查詢重複記錄 (排除最小 ROWID)

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
);

關鍵點解析

• ROWID：DB2 隱含唯一行標識，可直接用於定位具體物理行
• 元組 IN 子查詢：(col1, col2) IN (SELECT col1, col2 ...) 精確匹配組合鍵
• HAVING COUNT(*) > 1：識別重複組
• MIN(ROWID)：業務常規保留首筆 (最早插入) 記錄

效能優化建議

1. 大表先建立重複鍵臨時表：CREATE TEMP TABLE dup_keys AS SELECT key_cols, MIN(ROWID) AS keep_rid FROM big_table GROUP BY key_cols HAVING COUNT(*) > 1
2. 刪除時關聯臨時表：DELETE FROM big_table b WHERE EXISTS (SELECT 1 FROM dup_keys d WHERE b.key_cols = d.key_cols AND b.ROWID != d.keep_rid)
3. 配合 COMMITCOUNT 分批提交避免日誌爆滿
4. 刪除後執行 REORG TABLE big_table 回收空間 (DMS 表空間)

替代方案：MERGE (DB2 9.7+)

MERGE INTO people p
USING (
    SELECT peopleId, MIN(ROWID) AS keep_rid
    FROM people
    GROUP BY peopleId
    HAVING COUNT(*) > 1
) d ON p.peopleId = d.peopleId AND p.ROWID != d.keep_rid
WHEN MATCHED THEN DELETE;

語義更清晰，優化器可生成更優計劃。