資料庫冗余資料刪除實戰：PL/SQL 遊標法 vs SQL RANK() 分析函數法效能大比拚

什麼是冗余資料？

邏輯主鍵（如 LastName + FirstName）重複，但物理主鍵（ID）不同。範例表 Customers 含 3 組冗資料，共 10 列重複。

方法一：Oracle 官方範例 —— MIN(ID) 子查詢

DELETE FROM Customers
WHERE ID NOT IN (SELECT MIN(ID) FROM Customers GROUP BY LastName, FirstName);

缺點：需兩次全表掃描，大表極慢（500 萬列測試 1 小時未完成），且無法控制保留哪一列（只能保留最小 ID）。

方法二：PL/SQL 遊標逐列比對刪除

CREATE OR REPLACE PROCEDURE DeleteDuplicates (pCommitBatchSize IN INTEGER := 5000) IS
    CURSOR csr_Dup IS
        SELECT ID, LastName, FirstName
        FROM Customers
        WHERE (LastName, FirstName) IN (
            SELECT LastName, FirstName FROM Customers
            GROUP BY LastName, FirstName HAVING COUNT(*) > 1
        )
        ORDER BY LastName, FirstName;

    vLastName Customers.LastName%TYPE := NULL;
    vFirstName Customers.FirstName%TYPE := NULL;
    vCounter INTEGER := 0;
BEGIN
    FOR rec IN csr_Dup LOOP
        IF vLastName IS NULL OR rec.LastName != vLastName OR NVL(rec.FirstName,' ') != NVL(vFirstName,' ') THEN
            vLastName := rec.LastName; vFirstName := rec.FirstName;  -- 首列保留
        ELSE
            DELETE FROM Customers WHERE ID = rec.ID;  -- 後續重複刪除
            vCounter := vCounter + 1;
            IF MOD(vCounter, pCommitBatchSize) = 0 THEN COMMIT; END IF;
        END IF;
    END LOOP;
    IF vCounter > 0 THEN COMMIT; END IF;
    DBMS_OUTPUT.PUT_LINE(vCounter || ' duplicates deleted.');
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
    ROLLBACK;
END;
/

測試 500 萬列：56 秒。可控制保留首列，但需編寫程序、維護成本高。

方法三：SQL 分析函數 RANK() —— 單一陳述式、最高靈活度（推薦）

3.1 基礎版：保留最小 ID

DELETE FROM Customers
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID,
               RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS rn
        FROM Customers
    ) WHERE rn > 1
);

3.2 效能優化版：先過濾重複群組再計算 RANK

DELETE FROM Customers
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID,
               RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS rn
        FROM Customers
        WHERE (LastName, FirstName) IN (
            SELECT LastName, FirstName FROM Customers
            GROUP BY LastName, FirstName HAVING COUNT(*) > 1
        )
    ) WHERE rn > 1
);

測試結果：
基礎版 135 秒 → 優化版 47 秒（提升 65%），優於 PL/SQL 56 秒。

3.3 進階控制：保留最新、保留前 N 筆

• 保留最新記錄：ORDER BY RecDate DESC, ID
• 每組保留前 3 筆：
  HAVING COUNT(*) > 3 搭配 WHERE rn > 3

總結選型指南

結論：現代 Oracle/SQL Server/PostgreSQL 皆支援 RANK()，請優先採用「預過濾重複群組 + RANK()」單一 SQL 方案，兼顧效能、靈活度與可維護性。