Oracle 刪除表中重複資料完整方案：部分欄位重複、完全重複、保留最新記錄、大表高效能策略

重複資料分類

1. 部分欄位重複：關鍵業務鍵 (如 ID_NO, PHONE) 重複，其他欄位可能不同。
2. 完全重複：所有欄位值逐列相同（含 ROWID 不同）。

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

標準寫法

DELETE FROM target_table a
WHERE a.ROWID != (
  SELECT MAX(b.ROWID)
  FROM target_table b
  WHERE a.key_col1 = b.key_col1
    AND a.key_col2 = b.key_col2
);

大表高效能版 (CTE + 臨時表)

-- 1. 建立中間表只存保留的 ROWID
CREATE GLOBAL TEMPORARY TABLE tmp_keep_rowid ON COMMIT PRESERVE ROWS
AS
SELECT MAX(ROWID) AS keep_rid
FROM target_table
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1;

-- 2. 刪除不在中間表的重複行
DELETE FROM target_table a
WHERE a.ROWID NOT IN (SELECT keep_rid FROM tmp_keep_rowid)
  AND EXISTS (
    SELECT 1 FROM target_table b
    WHERE a.key_col1 = b.key_col1
      AND a.key_col2 = b.key_col2
      AND b.ROWID IN (SELECT keep_rid FROM tmp_keep_rowid)
  );

-- 3. 提交並清理
COMMIT;

優點：避免刪除時重複掃描、減少 UNDO/REDO、可分批提交。

場景二：完全重複 - DISTINCT 重建表

-- 方法 1：CTAS 重建 (需權限、索引/約束/權限需重建)
CREATE TABLE target_table_new AS
SELECT DISTINCT * FROM target_table;

DROP TABLE target_table;
RENAME target_table_new TO target_table;
-- 後續：重建索引、約束、授權、統計資訊

-- 方法 2：臨時表中轉 (保留原表物件屬性)
CREATE GLOBAL TEMPORARY TABLE tmp_dedup ON COMMIT PRESERVE ROWS
AS SELECT DISTINCT * FROM target_table;

DELETE FROM target_table;
INSERT INTO target_table SELECT * FROM tmp_dedup;
COMMIT;

場景三：保留最早/特定業務邏輯記錄

-- 保留最早建立 (假設有 CREATED_AT 欄位)
DELETE FROM target_table a
WHERE a.ROWID != (
  SELECT MIN(b.ROWID) KEEP (DENSE_RANK FIRST ORDER BY b.created_at)
  FROM target_table b
  WHERE a.key_col1 = b.key_col1
    AND a.key_col2 = b.key_col2
);

-- 保留狀態 = 'ACTIVE' 優先，其次最新
DELETE FROM target_table a
WHERE a.ROWID != (
  SELECT MAX(b.ROWID) KEEP (DENSE_RANK FIRST ORDER BY DECODE(b.status,'ACTIVE',1,2), b.updated_at DESC)
  FROM target_table b
  WHERE a.key_col1 = b.key_col1
    AND a.key_col2 = b.key_col2
);

防止重複資料產生的根治措施

效能基準參考 (萬筆級)

• 10 萬筆、2 個重複鍵：標準 DELETE ~3 秒；臨時表法 ~1 秒。
• 1000 萬筆、5% 重複：標準 DELETE 可能 > 30 分鐘並產生大量 UNDO；建議分批 DELETE ... WHERE ROWNUM <= 10000 循環，或使用 DBMS_REDEFINITION 線上重定義重建表。