資料庫冗餘資料刪除實戰：以 Oracle 為例的 PL/SQL 與分析函數兩大解法

資料庫中大量冗餘資料不僅損害資料品質，更拖垮查詢效能。本文以 Oracle 為例，定義冗餘資料、分析產生原因，並提供 PL/SQL 存儲程序 與 SQL 分析函數 兩種高效刪除方案，解決 Oracle 內建方案大數據量下效能不足的問題。

什麼是冗餘資料？

從使用者業務角度看，業務鍵值重複即為冗餘。例如 Customers 表以 ID 為主鍵（資料庫層面唯一），但 LastName + FirstName 組合重複，即為業務冗餘：

雖然主鍵不同，但業務實體相同，需保留一筆（通常保留最早建立或最新更新）。

冗餘產生兩大常見情境

1. 異構系統資料整合/合併載入。
2. 前端應用自動產生 Surrogate Key（如 Identity），缺乏業務唯一性檢核。

建立測試環境

DROP TABLE Customers CASCADE CONSTRAINTS;
CREATE TABLE Customers(
    Id INTEGER NOT NULL,
    LastName VARCHAR2(15) NOT NULL,
    FirstName VARCHAR2(10),
    Address VARCHAR2(20),
    City VARCHAR2(15),
    State CHAR(2),
    Zip VARCHAR2(10),
    Phone VARCHAR2(10),
    RecDate DATE,
    CONSTRAINT Customers_PK PRIMARY KEY (ID)
) TABLESPACE TALLYDATA;

-- 插入含冗餘測試資料
INSERT INTO Customers VALUES (1000, 'Bradley', 'Tom', '2450 3rd Str. #12', 'San Francisco', 'CA', '94114', '4156679230', TO_DATE('01-JAN-2000','DD-MON-YYYY'));
INSERT INTO Customers VALUES (1001, 'Stone', 'Tony', '12 Main St. Apt. 3', ...);

方案一：PL/SQL 存儲程序（適合複雜業務邏輯）

CREATE OR REPLACE PROCEDURE remove_dup_customers AS
    CURSOR c_dup IS
        SELECT MIN(Id) AS keep_id, LastName, FirstName
        FROM Customers
        GROUP BY LastName, FirstName
        HAVING COUNT(*) > 1;
BEGIN
    FOR r IN c_dup LOOP
        DELETE FROM Customers
        WHERE LastName = r.LastName
          AND FirstName = r.FirstName
          AND Id != r.keep_id;
    END LOOP;
    COMMIT;
END;
/

優點：邏輯清晰、易擴充業務規則（如保留最新 RecDate）。缺點：大數據量時游標逐筆處理較慢。

方案二：單一 SQL 陳述式 + 分析函數 RANK()（大數據量首選）

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

• PARTITION BY 依業務鍵分組。
• ORDER BY RecDate 決定保留哪一筆（最早/最新）。
• RANK() > 1 即為冗餘列，一次集合刪除，效能遠優於游標。

預防冗餘的長效機制

1. 於業務鍵建立 UNIQUE CONSTRAINT（如 UK_Cust_Name (LastName, FirstName)）。
2. ETL 階段加入去重邏輯（MERGE / INSERT ... WHERE NOT EXISTS）。
3. 定期排程執行上述刪除腳本清理歷史髒資料。

總結

針對大量冗餘資料，單一 SQL + 分析函數 是效能最佳選擇；複雜保留規則可採用 PL/SQL。建立唯一約束與 ETL 去重機制，從源頭杜絕冗餘產生。