Oracle 刪除重複數據高效方案：ROWID、臨時表、分析函數三大流派實戰

問題定義與檢測

無主鍵/唯一約束的表易產生全列重複行。以 EMPLOYEE(EMP_ID, EMP_NAME, SALARY) 為例，檢測重複：

-- 方式 1：GROUP HAVING
SELECT EMP_ID, EMP_NAME, SALARY, COUNT(*)
FROM EMPLOYEE
GROUP BY EMP_ID, EMP_NAME, SALARY
HAVING COUNT(*) > 1;

-- 方式 2：ROWID 關聯
SELECT * FROM EMPLOYEE E1
WHERE ROWID NOT IN (
  SELECT MAX(ROWID) FROM EMPLOYEE E2
  WHERE E1.EMP_ID = E2.EMP_ID
    AND E1.EMP_NAME = E2.EMP_NAME
    AND E1.SALARY = E2.SALARY
);

方案一：臨時表重建法（最簡單、鎖表久）

CREATE TABLE TEMP_EMP AS SELECT DISTINCT * FROM EMPLOYEE;
TRUNCATE TABLE EMPLOYEE;
INSERT INTO EMPLOYEE SELECT * FROM TEMP_EMP;
DROP TABLE TEMP_EMP;

• 優點：語法簡單，並行 DDL 加速
• 缺點：需獨佔表鎖，生成大量 Redo/Undo，外鍵/索引/權限/觸發器需重建
• 適用：中小表、維護窗口充足、無外鍵依賴

方案二：ROWID 子查詢刪除（原地去重、鎖表短）

-- 保留最大 ROWID
DELETE FROM EMPLOYEE E2
WHERE ROWID NOT IN (
  SELECT MAX(E1.ROWID) FROM EMPLOYEE E1
  WHERE E1.EMP_ID = E2.EMP_ID
    AND E1.EMP_NAME = E2.EMP_NAME
    AND E1.SALARY = E2.SALARY
);

-- 或保留最小 ROWID
DELETE FROM EMPLOYEE E2
WHERE ROWID < (
  SELECT MAX(E1.ROWID) FROM EMPLOYEE E1
  WHERE E1.EMP_ID = E2.EMP_ID
    AND E1.EMP_NAME = E2.EMP_NAME
    AND E1.SALARY = E2.SALARY
);

• 優點：原地刪除，保留索引/約束/權限，鎖定粒度細
• 缺點：相關子查詢執行計劃可能退化為 FILTER，大表性能差
• 優化：確保去重列上有索引，或用 /*+ HASH_AJ */ 提示

方案三：單掃描 GROUP BY ROWID（最高效、推薦）

DELETE FROM EMPLOYEE
WHERE ROWID NOT IN (
  SELECT MAX(ROWID) FROM EMPLOYEE
  GROUP BY EMP_ID, EMP_NAME, SALARY
);

• 原理：一次全表掃描按去重列分組，取每組最大 ROWID，集合差集刪除
• 優點：無相關子查詢，執行計劃通常為 HASH GROUP BY + HASH JOIN ANTI，線性複雜度
• 適用：大表去重首選，配合並行 DML ALTER SESSION ENABLE PARALLEL DML

進階方案：分析函數 ROW_NUMBER()（可見性最強）

DELETE FROM EMPLOYEE
WHERE ROWID IN (
  SELECT ROWID FROM (
    SELECT ROWID,
           ROW_NUMBER() OVER (PARTITION BY EMP_ID, EMP_NAME, SALARY ORDER BY ROWID) RN
    FROM EMPLOYEE
  ) WHERE RN > 1
);

• 窗口函數標準化寫法，易擴展（如保留最新修改時間）
• 12c+ 支持 DELETE ... WHERE ROWID IN (SELECT ...) 直接引用

大表分批刪除模板（防 Undo 爆滿、鎖升級）

CREATE OR REPLACE PROCEDURE PURGE_DUP_BATCH(
  P_TABLE  VARCHAR2,
  P_COLS   VARCHAR2,  -- 去重列列表，逗號分隔
  P_BATCH  NUMBER DEFAULT 50000
) AS
  V_SQL VARCHAR2(32767);
  V_CNT NUMBER;
BEGIN
  LOOP
    V_SQL := 'DELETE FROM ' || P_TABLE ||
             ' WHERE ROWID IN (' ||
             '   SELECT RID FROM (' ||
             '     SELECT ROWID RID, ROW_NUMBER() OVER (PARTITION BY ' || P_COLS || ' ORDER BY ROWID) RN' ||
             '     FROM ' || P_TABLE ||
             '   ) WHERE RN > 1 AND ROWNUM <= ' || P_BATCH ||
             ')';
    EXECUTE IMMEDIATE V_SQL;
    V_CNT := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN V_CNT = 0;
    DBMS_LOCK.SLEEP(1); -- 緩釋鎖競爭
  END LOOP;
END;
/
-- 調用
EXEC PURGE_DUP_BATCH('EMPLOYEE','EMP_ID,EMP_NAME,SALARY',20000);

預防重複：約束為王

1. 主鍵/唯一約束：源頭阻斷，ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID)
2. ETL 去重：數據入庫前用 DISTINCT/ROW_NUMBER 清洗
3. MERGE UPSERT：增量加載用 MERGE INTO ... USING ... ON (PK) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...

方案選型決策樹