Oracle 利用 ROWID 刪除重複資料：原理、步驟與效能優化完整指南

當嘗試建立唯一索引卻報 ORA-01452: cannot create unique index; duplicate keys found，或發現業務主鍵重複時，必須先清理重複資料。Oracle 每列固有的 ROWID（物理地址：檔案號、區塊號、列號）在全資料庫唯一，成為區分「內容相同但物理不同」重複列的關鍵。本文以 CZ 表為例，完整示範查找與刪除重複資料的標準寫法、效能優化版本、以及大表分批處理策略。

一、重複資料定義與 ROWID 核心原理

• 判斷標準：C1, C10, C20 三欄值完全相同視為重複。
• ROWID 特性：同一重複群組內，每列 ROWID 唯一；保留 ROWID 最大（或最小）的一列，刪除其餘即可達成去重。

二、標準刪除寫法（相關子查詢）

DELETE FROM cz a
WHERE rowid > (
    SELECT MIN(rowid)
    FROM   cz b
    WHERE  b.c1  = a.c1
    AND    b.c10 = a.c10
    AND    b.c20 = a.c20
);

邏輯：每組重複資料保留 ROWID 最小（最早插入）的一列。若資料量 < 10 萬列，效能可接受。

三、大表高效能寫法（CTAS + 交換）

當表超過百萬列，DELETE 會產生大量 Undo/Redo、索引碎片、High Water Mark 不下降。建議採用 Create Table As Select (CTAS) 重建：

-- 1. 建立去重後的新表
CREATE TABLE cz_dedup NOLOGGING PARALLEL 4 AS
SELECT *
FROM (
    SELECT c1, c10, c20,
           ROW_NUMBER() OVER (PARTITION BY c1, c10, c20 ORDER BY rowid) AS rn
    FROM   cz
)
WHERE rn = 1;

-- 2. 建立索引、約束、授權（同原表）
-- 3. 切換表名（需短暫鎖表）
RENAME cz TO cz_old;
RENAME cz_dedup TO cz;

-- 4. 驗證無誤後刪除舊表
DROP TABLE cz_old PURGE;

優點：幾乎不產生 Redo/Undo、自動重組索引、HWM 歸零、可平行加速。

四、分批刪除策略（無法停機切換時）

若業務不允許鎖表重建，採用分批 DELETE 並提交：

DECLARE
  v_deleted NUMBER := 0;
  v_batch   CONSTANT NUMBER := 5000;  -- 每批 5000 列
BEGIN
  LOOP
    DELETE FROM cz a
    WHERE rowid > (
        SELECT MIN(rowid)
        FROM   cz b
        WHERE  b.c1  = a.c1
        AND    b.c10 = a.c10
        AND    b.c20 = a.c20
    )
    AND ROWNUM <= v_batch;

    v_deleted := SQL%ROWCOUNT;
    COMMIT;
    EXIT WHEN v_deleted = 0;
    -- 可選：DBMS_LOCK.SLEEP(1) 降低爭用
  END LOOP;
END;
/

五、建立唯一索引前的最終驗證

-- 確認無重複
SELECT c1, c10, c20, COUNT(*)
FROM   cz
GROUP BY c1, c10, c20
HAVING COUNT(*) > 1;

-- 若回傳 0 列，可安全建立唯一索引
CREATE UNIQUE INDEX uq_cz_c1_c10_c20 ON cz(c1, c10, c20);

六、進階：利用 ROWID 進行列級鎖定與異動追蹤

• SELECT ... FOR UPDATE NOWAIT 結合 ROWID 可實現精準列鎖。
• 觸發器中 :OLD.ROWID、:NEW.ROWID 可記錄異動前後物理位置，配合 DBMS_ROWID 解析檔案/區塊號，支援區塊層級審計。

掌握 ROWID 去重技巧，不僅能解決唯一索引建立受阻，更能在資料遷移、ETL 去重、異常資料清理等場景遊刃有餘。記住：小表用 DELETE、大表用 CTAS、無法停機用分批，三招應對所有去重挑戰。