Oracle 自訂函數效能實測：引擎切換成本與實作差異分析

背景知識：PL/SQL 雙引擎架構

Oracle 內部有兩大執行引擎：

• SQL 引擎：處理標準 SQL 語句。
• PL/SQL 引擎：處理程序性程式碼。

兩引擎切換會產生 CPU 額外開銷。FORALL/BULK COLLECT 等集合操作可減少切換，大幅提升效能。

實測場景

• 表 TA_CUSTOMER 約 2 億筆資料。
• 欄位：BIRTHDAY（多為 NULL）、CERTIFICATE_TYPE、CERTIFICATE_NO。
• 需求：從身分證號擷取出生年份（身分證 15/18 碼，舊版 15 碼全數字，新版 18 碼前 17 碼數字+末碼數字/X）。
• 資料品質差：CERTIFICATE_TYPE='0' 代表身分證，但錄入錯誤導致非 '0' 也可能是身分證，反之亦然。

測試 SQL

-- 基準：單純字串連接
SELECT COUNT(t.certificate_type || t.certificate_no || t.birthday)
FROM ta_customer t WHERE ROWNUM < 2000000;

-- 版本 1：逐字元 ASCII 檢查
SELECT COUNT(f_extract_birthday_v1(t.certificate_type, t.certificate_no, t.birthday))
FROM ta_customer t WHERE ROWNUM < 2000000;

-- 版本 2：CAST 數值轉換 + 例外處理
SELECT COUNT(f_extract_birthday_v2(t.certificate_type, t.certificate_no, t.birthday))
FROM ta_customer t WHERE ROWNUM < 2000000;

執行時間對比（秒）

※ 版本 2 反而較慢，因例外處理機制開銷極大。

關鍵發現

1. 自訂函數效能普遍劣於內建函數：基準僅字串連接 8.5 秒，加入函數呼叫即 2.3-6.8 倍。
2. 演算法選擇影響巨大：逐字元檢查（版本 1）優於例外處理（版本 2），後者觸發大量例外拋捕獲。
3. 引擎切換成本真實存在：每列呼叫函數 = 每列 SQL→PL/SQL 切換。

優化建議

• 能用純 SQL 完成（REGEXP_SUBSTR、SUBSTR、CASE）優先用 SQL，避免函數呼叫。
• 必須用函數時，考慮 DETERMINISTIC、RESULT_CACHE、PRAGMA UDF (12c+) 減少開銷。
• 批次處理改用 FORALL + 集合，或改寫為 Pipeline Table Function。
• 身分證解析邏輯可封裝為 DETERMINISTIC 函數並建立 Function-based Index 支援查詢。

附錄：兩版本核心程式碼

版本 1 - 逐字元 ASCII 檢查

CREATE OR REPLACE FUNCTION f_extract_birthday_v1(...)
RETURN VARCHAR2 IS
  c PLS_INTEGER; i PLS_INTEGER := 1; len PLS_INTEGER;
  trans BOOLEAN := FALSE; ret VARCHAR2(4);
BEGIN
  len := LENGTH(cert_no);
  IF len = 15 THEN
    WHILE i <= 15 LOOP
      c := ASCII(SUBSTR(cert_no, i, 1));
      IF c < 48 OR c > 57 THEN EXIT; END IF;
      i := i + 1;
    END LOOP;
    IF i = 16 THEN trans := TRUE; ret := '19' || SUBSTR(cert_no, 7, 2); END IF;
  ELSIF len = 18 THEN
    WHILE i <= 17 LOOP ... END LOOP;
    c := ASCII(SUBSTR(cert_no, 18, 1));
    IF i = 18 AND (c = 88 OR c = 120 OR (c BETWEEN 48 AND 57)) THEN
      trans := TRUE; ret := SUBSTR(cert_no, 7, 4);
    END IF;
  END IF;
  -- 後續 birthday 兜底邏輯略
  RETURN ret;
END;

版本 2 - CAST + EXCEPTION（不推薦）

CREATE OR REPLACE FUNCTION f_extract_birthday_v2(...)
RETURN VARCHAR2 IS
  val NUMBER; ret VARCHAR2(4);
BEGIN
  IF cert_type = '0' THEN
    BEGIN
      val := CAST(cert_no AS NUMBER);  -- 15 碼
      ret := '19' || SUBSTR(cert_no, 7, 2);
    EXCEPTION WHEN VALUE_ERROR THEN NULL; END;
    BEGIN
      val := CAST(SUBSTR(cert_no,1,17) AS NUMBER); -- 18 碼
      -- 檢查第 18 碼
      ret := SUBSTR(cert_no, 7, 4);
    EXCEPTION WHEN VALUE_ERROR THEN NULL; END;
  END IF;
  -- birthday 兜底同理用 CAST + EXCEPTION
  RETURN ret;
END;