Oracle 數據庫：分組後更新每組最早記錄的正確寫法

問題場景

表 org_info 按 ID 分組，需將每組 OCCUR_DATE 最早的一條記錄的 OP_TYPE 更新為 1。嘗試使用子查詢更新報錯 ORA-01732。

錯誤原因

Oracle 不允許直接更新包含分析函數（如 ROW_NUMBER()）的內聯視圖/子查詢，因其被視為「非鍵保留表」，無法確定唯一行對應關係。

正確解決方案：使用 ROWID 關聯更新

UPDATE org_info
SET op_type = 1
WHERE rowid IN (
    SELECT rid
    FROM (
        SELECT rowid AS rid,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY occur_date ASC) AS rn
        FROM org_info
    )
    WHERE rn = 1
);

關鍵點解析

1. ROWID 是物理地址唯一標識，繞過「鍵保留」限制，直接定位行。
2. 子查詢先計算 ROW_NUMBER，外層篩選 rn=1 獲取目標 ROWID 集合。
3. IN 子查詢執行計劃高效，Oracle 內部優化為半連接。

替代寫法：MERGE 語句（Oracle 10g+）

MERGE INTO org_info t
USING (
    SELECT rowid AS rid,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY occur_date ASC) AS rn
    FROM org_info
) s
ON (t.rowid = s.rid AND s.rn = 1)
WHEN MATCHED THEN UPDATE SET t.op_type = 1;

MERGE 語義更清晰，支持複雜條件，大數據量時性能可通過並行提示優化：/*+ PARALLEL(4) */。

性能優化建議

• 確保 ID、OCCUR_DATE 有索引，加速分組排序。
• 大表分批更新：WHERE rowid IN (SELECT ... WHERE rownum <= 10000) 循環提交。
• 監控 UNDO 表空間，避免大事務撐爆。

驗證腳本

SELECT id, occur_date, op_type
FROM org_info
ORDER BY id, occur_date;

確認每組第一條 op_type=1，其餘為原值或 0。

掌握 ROWID 更新模式，是 Oracle DBA/開發處理「分組 Top-N 更新」的必備技能。