重複資料治理是資料庫維護常態工作。Oracle 提供 ROWID 偽欄位唯一識別每一列,配合分組統計、子查詢、分析函數,可精準識別並清理重複資料。本文系統整理單欄位/多欄位重複查詢與刪除、保留首筆/末筆策略、ROWID 定位原理、效能優化與大量資料分批處理技巧。
people_id,或多欄位組合 (people_id, seq))分組,COUNT(*) > 1 即為重複群組。MIN(ROWID)(最早插入)或 MAX(ROWID)(最新插入),刪除其餘。SELECT * FROM people
WHERE people_id IN (
SELECT people_id FROM people
GROUP BY people_id
HAVING COUNT(people_id) > 1
);
DELETE FROM people
WHERE people_id IN (
SELECT people_id FROM people
GROUP BY people_id
HAVING COUNT(people_id) > 1
)
AND ROWID NOT IN (
SELECT MIN(ROWID) FROM people
GROUP BY people_id
HAVING COUNT(people_id) > 1
);
SELECT * FROM vitae a
WHERE (a.people_id, a.seq) IN (
SELECT people_id, seq FROM vitae
GROUP BY people_id, seq
HAVING COUNT(*) > 1
);
DELETE FROM vitae a
WHERE (a.people_id, a.seq) IN (
SELECT people_id, seq FROM vitae
GROUP BY people_id, seq
HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
SELECT MIN(ROWID) FROM vitae
GROUP BY people_id, seq
HAVING COUNT(*) > 1
);
SELECT * FROM vitae a
WHERE (a.people_id, a.seq) IN (
SELECT people_id, seq FROM vitae
GROUP BY people_id, seq
HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
SELECT MIN(ROWID) FROM vitae
GROUP BY people_id, 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;
-- 標記重複行序號
WITH dup_cte AS (
SELECT rowid AS rid,
ROW_NUMBER() OVER (PARTITION BY people_id ORDER BY rowid) AS rn
FROM people
)
DELETE FROM people
WHERE ROWID IN (SELECT rid FROM dup_cte WHERE rn > 1);
PARTITION BY 可擴展為多欄位:PARTITION BY people_id, seq。ORDER BY rowid 保留最早插入;改 ORDER BY created_date DESC 可保留最新。DECLARE
v_deleted NUMBER := 0;
BEGIN
LOOP
DELETE FROM people
WHERE ROWID IN (
SELECT rid FROM (
SELECT ROWID AS rid,
ROW_NUMBER() OVER (PARTITION BY people_id ORDER BY ROWID) AS rn
FROM people
) WHERE rn > 1 AND ROWNUM <= 10000
);
v_deleted := v_deleted + SQL%ROWCOUNT;
COMMIT;
EXIT WHEN SQL%ROWCOUNT = 0;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total deleted: ' || v_deleted);
END;
/
CREATE TABLE people_new AS
SELECT * FROM (
SELECT p.*, ROW_NUMBER() OVER (PARTITION BY people_id ORDER BY ROWID) AS rn
FROM people p
) WHERE rn = 1;
-- 驗證無誤後
DROP TABLE people;
RENAME people_new TO people;
-- 重建索引、約束、授權、同義詞
| 陷阱 | 說明 |
|---|---|
| ROWID 跨遷移失效 | 表遷移、匯入匯出、ROW MOVEMENT ENABLE 後 ROWID 會變,不可跨作業持久化保存 |
| 子查詢效能劣化 | 大表多次掃描,改用 ROW_NUMBER() 或 CTAS |
| 刪除觸發器/外鍵阻塞 | 先停用相關觸發器/外鍵,清理後重新啟用 |
| 保留策略錯誤 | 業務上可能需保留「最新」而非「最早」,須確認 ORDER BY 條件 |
| 主鍵/唯一約束未建立 | 重複資料根因常為缺約束,清理後務必加上 PK/UK |
Oracle 重複資料清理的核心在於:以業務鍵分組識別重複群組 → 以 ROWID 定位物理列 → 依保留策略刪除多餘列。小表用經典 IN/NOT IN 子查詢即可;大表必須改用 ROW_NUMBER() 分析函數單掃描,並配合分批提交或 CTAS 重建。最重要的事後動作:**補上缺失的主鍵/唯一約束,從源頭杜絕重複產生**。