Oracle UPDATE 語法詳解與性能優化深度分析

測試場景與數據模型

• 客戶表 customers：customer_id（PK）、city_name、customer_type。
• 臨時表 tmp_cust_city：包含 20% 客戶的準確城市信息與 VIP 類型。

五種 UPDATE 寫法與性能對比

1. 最簡單形式：常量更新

UPDATE customers
SET city_name = '北京'
WHERE customer_id < 1000;

適用於固定值批量更新，執行計劃通常為全表掃描或索引範圍掃描，性能最優。

2. 多表關聯 UPDATE：僅在 WHERE 子句連接

UPDATE customers a
SET customer_type = '01'
WHERE EXISTS (
  SELECT 1 FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
);

EXISTS 半連接，通常比 IN 高效，優化器可轉為 Hash Join 或 Nested Loop。

3. 多表關聯 UPDATE：被修改值來自另一表（單列）

UPDATE customers a
SET city_name = (
  SELECT b.city_name FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
)
WHERE EXISTS (
  SELECT 1 FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
);

性能隱患：子查詢在 SELECT 列表與 WHERE 條件中各出現一次，導致對 tmp_cust_city 兩次掃描/索引訪問。

4. 多表關聯 UPDATE：一次更新多列（推薦寫法）

UPDATE customers a
SET (city_name, customer_type) = (
  SELECT b.city_name, b.customer_type
  FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
)
WHERE EXISTS (
  SELECT 1 FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
);

單次子查詢返回多列，僅掃描一次 tmp_cust_city，性能優於寫法 3。

5. 缺失值處理：NVL 防止「cannot update to NULL」錯誤

若 tmp_cust_city 不包含所有客戶，子查詢可能返回 NULL，導致報錯 ORA-01407。解決方案：

UPDATE customers a
SET city_name = NVL((
  SELECT b.city_name FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
), city_name)  -- 保持原值
WHERE EXISTS (...);

或使用 MERGE 語句（Oracle 9i+），語法更清晰、性能更穩健：

MERGE INTO customers a
USING tmp_cust_city b
ON (a.customer_id = b.customer_id)
WHEN MATCHED THEN
  UPDATE SET a.city_name = b.city_name, a.customer_type = b.customer_type;

性能優化關鍵點總結

• 確保連接列（customer_id）有索引，支援高效 Nested Loop 或 Hash Join。
• 大批量更新時考慮並行 DML：ALTER SESSION ENABLE PARALLEL DML + UPDATE /*+ PARALLEL(a,4) */ ...。
• 避免不帶 WHERE 條件的全表更新，除非確需更新所有行。