Oracle 刪除重複資料完整指南：部分欄位重複與完全重複兩大場景解決方案

重複資料的兩大分類

1. 部分欄位重複：關鍵業務鍵 (如 ID、代碼) 重複，但其他欄位可能不同
2. 完全重複：整列資料所有欄位值完全相同

針對不同場景需採用不同策略，本文提供高效能、可保留最新資料的標準解法。

場景一：部分欄位重複 (保留最新一筆)

步驟 1：識別重複群組

-- 查詢重複的業務鍵組合
SELECT COL1, COL2, COUNT(*)
FROM TABLE_NAME
GROUP BY COL1, COL2
HAVING COUNT(*) > 1;

步驟 2：低效寫法 (不建議大表使用)

-- 直接刪除：效能極差，可能鎖死資料庫
DELETE FROM TABLE_NAME A
WHERE (COL1, COL2) IN (
    SELECT COL1, COL2 FROM TABLE_NAME
    GROUP BY COL1, COL2 HAVING COUNT(*) > 1
);

步驟 3：中等效能寫法 (臨時表預存重複鍵)

-- 建立臨時表儲存重複鍵
CREATE GLOBAL TEMPORARY TABLE TMP_DUP_KEYS ON COMMIT PRESERVE ROWS
AS SELECT COL1, COL2 FROM TABLE_NAME
   GROUP BY COL1, COL2 HAVING COUNT(*) > 1;

-- 刪除重複 (仍會刪除該鍵下所有列，含最新)
DELETE FROM TABLE_NAME A
WHERE (A.COL1, A.COL2) IN (SELECT COL1, COL2 FROM TMP_DUP_KEYS);

步驟 4：最佳寫法——保留 ROWID 最大 (最新) 的那一筆

Oracle 每列隱含 ROWID，物理位置編碼，越新產生的 ROWID 越大 (近似)。

-- 單一 SQL 完成：刪除重複鍵下非最大 ROWID 的列
DELETE FROM TABLE_NAME A
WHERE A.ROWID != (
    SELECT MAX(B.ROWID) 
    FROM TABLE_NAME B 
    WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2
);

步驟 5：大表高效能版 (臨時表預計算 MAX ROWID)

-- 1. 建立臨時表：每個業務鍵保留的目標 ROWID
CREATE GLOBAL TEMPORARY TABLE TMP_KEEP_ROWID ON COMMIT PRESERVE ROWS
AS 
SELECT COL1, COL2, MAX(ROWID) AS KEEP_ROWID
FROM TABLE_NAME
GROUP BY COL1, COL2;

-- 2. 刪除非保留列
DELETE FROM TABLE_NAME A
WHERE A.ROWID != (
    SELECT B.KEEP_ROWID 
    FROM TMP_KEEP_ROWID B 
    WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2
);

-- 3. 提交並清理
COMMIT;
DROP TABLE TMP_KEEP_ROWID;

• 子查詢只掃描小臨時表，不重複掃描大表
• Global Temporary Table 併發安全、自動清理
• 大表 (億級) 從小時級降至分鐘級

場景二：完全重複列 (整列資料相同)

方法 1：DISTINCT 去重 + 表重建 (標準做法)

-- 1. 建立去重後的臨時表
CREATE TABLE TABLE_NAME_DEDUP AS
SELECT DISTINCT * FROM TABLE_NAME;

-- 2. 刪除原表 (或 RENAME)
DROP TABLE TABLE_NAME;

-- 3. 將去重資料寫回 (可選：重建索引、約束、授權)
-- 這裡假設同名重建
CREATE TABLE TABLE_NAME AS SELECT * FROM TABLE_NAME_DEDUP;

-- 4. 清理
DROP TABLE TABLE_NAME_DEDUP;

方法 2：直接插入備份表 (保留原表結構)

-- 前置：建立同結構備份表
CREATE TABLE TABLE_NAME_BAK AS SELECT * FROM TABLE_NAME WHERE 1=0;

-- 去重插入
INSERT INTO TABLE_NAME_BAK SELECT DISTINCT * FROM TABLE_NAME;

-- 切換 (需停機維護窗口)
-- RENAME TABLE_NAME TO TABLE_NAME_OLD;
-- RENAME TABLE_NAME_BAK TO TABLE_NAME;
-- 重建索引、約束、授權、同義詞

方法 3：單表去重 (無法 DROP 表時)

-- 利用 ROWID 刪除完全重複中較舊的列
DELETE FROM TABLE_NAME A
WHERE A.ROWID > (
    SELECT MIN(B.ROWID) 
    FROM TABLE_NAME B 
    WHERE A.COL1 = B.COL1 
      AND A.COL2 = B.COL2
      -- 這裡需列出所有欄位比較，或用 ROWID 直接比較
      AND A.ROWID != B.ROWID
);
-- 注意：完全重複時 ROWID 比較需謹慎，建議用方法 1/2

效能對比與選型建議

關鍵技術細節補充

ROWID 特性與限制

• ROWID 編碼：物件號 + 相對檔號 + 區塊號 + 列號
• 同一表中，較晚插入的列 ROWID 通常較大 (但非絕對，受分區、遷移、縮表影響)
• 最可靠的「最新」判斷：業務時間欄位 (如 CREATE_TIME、UPDATE_TIME)，而非 ROWID
• 若有時間欄位，改用 MAX(CREATE_TIME) 替代 MAX(ROWID)

分批刪除模板 (避免長交易、UNDO 爆滿、鎖升級)

DECLARE
  V_DELETED NUMBER := 0;
  V_BATCH_SIZE CONSTANT NUMBER := 10000;
BEGIN
  LOOP
    DELETE FROM TABLE_NAME A
    WHERE A.ROWID != (
        SELECT MAX(B.ROWID) FROM TABLE_NAME B
        WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2
    )
    AND ROWNUM <= V_BATCH_SIZE;
    
    V_DELETED := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN V_DELETED = 0;
  END LOOP;
END;
/
-- 每批 1 萬列提交，避免長交易風險

刪除前必做：備份與驗證

1. CREATE TABLE TABLE_NAME_BAK_YYYYMMDD AS SELECT * FROM TABLE_NAME;
2. 抽樣驗證備份表資料完整性
3. 確認刪除邏輯在測試環境正確
4. 生產環境維護窗口執行，通知業務方

總結

1. 部分欄位重複：用 ROWID != MAX(ROWID) 保留最新，大表必配臨時表預計算
2. 完全重複：CREATE TABLE AS SELECT DISTINCT 重建表最乾淨
3. 大表分批提交：避免 UNDO 爆滿、鎖升級、長交易阻塞
4. 業務時間欄位優於 ROWID：有時間欄位務必用時間判斷新舊
5. 刪除前備份、測試驗證、維護窗口執行：資料無價，謹慎為上

1. 上一篇文章： Oracle 資料庫檔案遷移實戰：Windows 環境跨磁碟移動資料檔完整步驟
2. 下一篇文章： Oracle 視圖核心價值：簡化查詢、行列級安全性、邏輯資料獨立性

Oracle 資料庫基礎

Navicat for Oracle 安裝與連線配置完整教學

工具簡介 Navicat for Oracle 是一套功能強

Oracle 最大可用性架構（MAA）深度解析

什麼是 MAA？ 最大可用性架構 是 Oracle 基於成熟

Statspack 實戰診斷 Oracle 效能瓶頸：從安裝到分析報告全流程

為什麼需要 Statspack Statspack 是 Or

基於開放標準 (JSR 168 / WSRP) 建構位置資訊 Portlet 完整實戰

前言：企業入口網站與位置智慧的融合 企業入口網站已成為資訊存