Oracle 刪除重複數據完全指南：部分列重複與全行重複的高效方案

場景分類

• 部分列重複：業務鍵（如訂單號+商品碼）重複，其他列可能不同
• 全行重複：所有列數據完全相同

一、部分列重複數據處理

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
);

⚠️ 子查詢執行計劃差，大表易導致全表掃描鎖表、UNDO 爆漲。

3. 高效方案：臨時表中轉

-- 步驟 1：創建臨時表存儲重複鍵值
CREATE TABLE tmp_dup_keys AS
SELECT col1, col2
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

-- 步驟 2：建立索引加速連接
CREATE INDEX idx_tmp_dup ON tmp_dup_keys(col1, col2);

-- 步驟 3：高效刪除
DELETE FROM table_name a
WHERE EXISTS (
  SELECT 1 FROM tmp_dup_keys b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);

-- 步驟 4：提交並清理
COMMIT;
DROP TABLE tmp_dup_keys;

4. 保留最新一條（基於 ROWID）

Oracle 隱藏列 ROWID 唯一標識每行，值越大越新（一般情況）。

-- 查詢重複行（保留 ROWID 最大者）
SELECT a.rowid, a.*
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
);

-- 刪除重複，保留最新
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. 臨時表優化版（大數據量推薦）

CREATE TABLE tmp_keep AS
SELECT col1, col2, MAX(rowid) AS keep_rowid
FROM table_name
GROUP BY col1, col2;

DELETE FROM table_name a
WHERE a.rowid != (
  SELECT b.keep_rowid FROM tmp_keep b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);
COMMIT;
DROP TABLE tmp_keep;

二、全行重複數據處理

方案：DISTINCT 去重 + 表重建

-- 1. 創建去重臨時表
CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM table_name;

-- 2. 刪除原表（或 TRUNCATE 保留結構/權限/索引）
DROP TABLE table_name;
-- 或 TRUNCATE TABLE table_name;  -- 需先禁用外鍵

-- 3. 重建表並導入數據
CREATE TABLE table_name AS SELECT * FROM tmp_dedup;
-- 重建索引、約束、授權、觸發器...

-- 4. 清理
DROP TABLE tmp_dedup;

替代方案：INSERT 去重（保留原表結構）

CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM table_name;
TRUNCATE TABLE table_name;
INSERT INTO table_name SELECT * FROM tmp_dedup;
COMMIT;
DROP TABLE tmp_dedup;

性能對比與選型建議

進階技巧

• 分批刪除：大事務拆分小批次，避免 UNDO 爆漲、鎖升級

  LOOP
    DELETE FROM table_name a
    WHERE a.rowid IN (
      SELECT rowid FROM (
        SELECT rowid FROM table_name a
        WHERE a.rowid != (SELECT MAX(rowid) FROM table_name b WHERE a.col1=b.col1 AND a.col2=b.col2)
        AND ROWNUM <= 10000
      )
    );
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;

• 並行去重：CREATE TABLE new_t PARALLEL 8 AS SELECT DISTINCT * FROM old_t;
• 唯一約束輔助：添加唯一約束 ALTER TABLE t ADD CONSTRAINT uk_t UNIQUE (col1,col2) EXCEPTIONS INTO exceptions;，再處理 exceptions 表
• 備份優先：任何刪除操作前，確保有可恢復的備份或 Flashback Table 窗口

總結

1. 識別重複類型：部分列 vs 全行
2. 小表用 DISTINCT 重建，大表用 ROWID + 臨時表
3. 保留最新記錄利用 MAX(ROWID)
4. 生產環境必須：備份 → 驗證 → 分批 → 提交 → 重建索引/統計信息