Oracle SQL 進階技巧：表 B 分組聚合更新表 A 的多種寫法與效能對比

問題描述

表 A (CLM001 PK, SCORE) 初始分數為 0。表 B (ID, CLM001, SCORE) 含多筆明細。需將表 B 依 CLM001 分組求和，加至表 A 對應記錄的 SCORE。

範例資料：

預期結果：A 表 001→36、002→110、003→0、004→240。

方案一：WITH 子查詢（標準 SQL，Oracle 9i+ 支援）

WITH r AS (
    SELECT CLM001, SUM(SCORE) AS total
    FROM B
    GROUP BY CLM001
)
UPDATE A
SET SCORE = (SELECT total FROM r WHERE r.CLM001 = A.CLM001)
WHERE EXISTS (SELECT 1 FROM r WHERE r.CLM001 = A.CLM001);

• 優點：可讀性強、符合 ANSI SQL、優化器易轉換為連接
• 缺點：需 PL/SQL 區塊包裹始可在程式中執行

方案二：PL/SQL 游標迴圈（程式化邏輯）

CREATE OR REPLACE PROCEDURE p_test AS
    v_clm001 A.CLM001%TYPE;
    CURSOR c_record IS SELECT DISTINCT CLM001 FROM B;
BEGIN
    OPEN c_record;
    LOOP
        FETCH c_record INTO v_clm001;
        EXIT WHEN c_record%NOTFOUND;
        UPDATE A
        SET SCORE = (SELECT SUM(SCORE) FROM B WHERE CLM001 = v_clm001)
        WHERE CLM001 = v_clm001;
    END LOOP;
    CLOSE c_record;
END;
/

• 優點：邏輯直觀、易加入業務邏輯、支援複雜流程控制
• 缺點：逐行處理、上下文切換開銷大、大量資料效能差

方案三：關聯更新（相關子查詢）

UPDATE A
SET SCORE = SCORE + (
    SELECT SUM(SCORE) 
    FROM B 
    WHERE B.CLM001 = A.CLM001
)
WHERE EXISTS (SELECT 1 FROM B WHERE B.CLM001 = A.CLM001);

• 優點：單一 SQL 完成、無需 PL/SQL、優化器可轉為 Hash Join/Aggregate
• 缺點：若 B 表極大且無索引，全表掃描重複執行

方案四：合併更新（MERGE / 視圖法）— 最佳實踐

MERGE INTO A
USING (SELECT CLM001, SUM(SCORE) AS total FROM B GROUP BY CLM001) B_agg
ON (A.CLM001 = B_agg.CLM001)
WHEN MATCHED THEN UPDATE SET A.SCORE = A.SCORE + B_agg.total;

或建立視圖簡化：

CREATE VIEW B_SUM AS SELECT CLM001, SUM(SCORE) AS total FROM B GROUP BY CLM001;
UPDATE A SET SCORE = SCORE + (SELECT total FROM B_SUM WHERE CLM001 = A.CLM001)
WHERE EXISTS (SELECT 1 FROM B_SUM WHERE CLM001 = A.CLM001);

• 優點：聚合一次、連接更新、執行計畫穩定、可維護性強
• 效能：大量資料下顯著優於方案二、三

效能對比測試框架

-- 1. 建立測試資料（100萬筆 B 表）
CREATE TABLE B AS 
SELECT ROWNUM ID, MOD(ROWNUM, 10000) CLM001, DBMS_RANDOM.VALUE(1,100) SCORE
FROM DUAL CONNECT BY LEVEL <= 1000000;

-- 2. 收集統計
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'B');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'A');

-- 3. 開啟 SQL Trace
ALTER SESSION SET SQL_TRACE = TRUE;

-- 4. 執行各方案，比較：
--    - Elapsed Time
--    - Consistent Gets
--    - Physical Reads
--    - Plan Hash Value

核心原則總結

避坑指南

• 忘記 WHERE EXISTS：導致無對應 B 記錄的 A 行 SCORE 變 NULL
• 直接 SET SCORE = 子查詢：應為 SCORE = SCORE + 子查詢 累加
• B 表無索引：大表分組聚合必建 CLM001 索引
• 並發衝突：高併發環境加 SELECT ... FOR UPDATE 或序列化

「用集合思維寫 SQL，用視圖封裝邏輯，用 MERGE 實現高效更新」—— 這是 Oracle 開發者的進階心法。