Oracle 大表高效去重刪除策略：ROWID 保留法

傳統去重痛點

大表 (億級) 使用 `DELETE ... WHERE ROWID NOT IN (SELECT MIN(ROWID) ... GROUP BY key)` 會導致：

• 巨大 Undo 產生
• 全表掃描多次
• 鎖升級、執行時間不可控

創新策略：四步走「保留不重複 + 保留重複一條 → 合併」

步驟 1：建立不重複資料表

CREATE TABLE xxfgs_sig AS
SELECT imeid,
       MAX(dn) dn,
       MAX(xlh) xlh,
       MAX(pro_name) pro_name,
       MAX(area_name) area_name,
       MAX(brand) brand,
       MAX(m_type) m_type
FROM xxfgs
GROUP BY imeid
HAVING COUNT(*) < 2;  -- 僅保留本來就不重複的鍵

步驟 2：建立重複鍵的保留 ROWID 表

CREATE TABLE xxfgs_row AS
SELECT MAX(rowid) AS rowdata, imeid
FROM xxfgs
GROUP BY imeid
HAVING COUNT(*) > 1;  -- 每個重複組保留一個 ROWID (最大值)

步驟 3：刪除原表中非保留 ROWID (兩種寫法)

寫法 A：NOT IN (適合中等表)

DELETE FROM xxfgs
WHERE rowid NOT IN (SELECT rowdata FROM xxfgs_row);

寫法 B：關聯子查詢 (適合大表，配合索引)

DELETE FROM xxfgs a
WHERE a.rowid <> (SELECT rowdata FROM xxfgs_row b WHERE a.imeid = b.imeid);

建議在 `xxfgs_row(imeid)` 建立索引加速關聯。

步驟 4：合併不重複資料與保留的重複資料

INSERT INTO xxfgs_sig
SELECT * FROM xxfgs a
WHERE a.rowid IN (SELECT rowdata FROM xxfgs_row);

-- 重命名/切換
DROP TABLE xxfgs;
RENAME xxfgs_sig TO xxfgs;

效能優化關鍵點

12c+ 替代方案：ROW_NUMBER() 窗口函數

DELETE FROM xxfgs
WHERE rowid IN (
  SELECT rowid FROM (
    SELECT rowid,
           ROW_NUMBER() OVER (PARTITION BY imeid ORDER BY rowid) AS rn
    FROM xxfgs
  ) WHERE rn > 1
);
-- 單條 SQL 完成，優化器可選擇 HASH GROUP BY 避免排序