萬盛學電腦網

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

Oracle 查詢與刪除重複資料 SQL 完整技巧集:單欄位、多欄位、ROWID 定位

前言

重複資料治理是資料庫維護常態工作。Oracle 提供 ROWID 偽欄位唯一識別每一列,配合分組統計、子查詢、分析函數,可精準識別並清理重複資料。本文系統整理單欄位/多欄位重複查詢與刪除、保留首筆/末筆策略、ROWID 定位原理、效能優化與大量資料分批處理技巧。

一、核心概念:ROWID 與重複定義

  • ROWID:Oracle 物理地址編碼,唯一標識一列資料(物件編號、檔案號、區塊號、列號)。
  • 重複定義:依業務鍵(單欄位如 people_id,或多欄位組合 (people_id, seq))分組,COUNT(*) > 1 即為重複群組。
  • 保留策略:通常保留 MIN(ROWID)(最早插入)或 MAX(ROWID)(最新插入),刪除其餘。

二、單欄位重複處理

2.1 查詢重複記錄

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

2.2 刪除重複,保留 ROWID 最小者(最早插入)

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

三、多欄位組合重複處理

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

3.2 刪除重複,保留 ROWID 最小者

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

3.3 查詢重複但排除保留行(供人工審核)

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

四、GROUP BY HAVING 經典模式(單表統計重複)

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

-- 多欄位
SELECT name, sex, COUNT(*) FROM a GROUP BY name, sex HAVING COUNT(*) > 1;

五、大量資料效能優化方案

5.1 使用分析函數 ROW_NUMBER()(單次掃描,效能優)

-- 標記重複行序號
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 可保留最新。

5.2 分批提交避免 UNDO 爆滿/鎖定過久

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

5.3 CTAS 重建法(極大量重複、表可離線)

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 重建。最重要的事後動作:**補上缺失的主鍵/唯一約束,從源頭杜絕重複產生**。

copyright © 萬盛學電腦網 all rights reserved