PL/SQL 程式碼執行涉及兩大引擎:SQL 引擎處理 SQL 陳述式,PL/SQL 引擎處理程序邏輯。兩者切換(Context Switch)會產生 CPU 額外開銷。本文以 200 萬筆客戶資料實測,量化自訂函數與內建函數的效能差距。
實測環境
- 資料表
ta_customer:200 萬列,含 birthday、certificate_type、certificate_no。
- 伺服器:Windows 2003,Intel Xeon 5140 2.33GHz ×4,4GB RAM。
- 測試 SQL:
SELECT COUNT(f_extract_birthday(...)) FROM ta_customer WHERE ROWNUM < 2000000
測試結果
| 測試項目 | 耗時 (秒) | 倍數 |
純字串串接 cert_type || cert_no || birthday | 8.56 | 1.0x (基準) |
| 自訂函數版本 1(逐字元檢核) | 19.84 | 2.3x |
| 自訂函數版本 2(CAST + EXCEPTION 捕捉) | 57.95 | 6.8x |
關鍵發現
- 自訂函數平均比內建運算慢 2-7 倍,主因是每行觸發 PL/SQL→SQL 引擎切換。
- 版本 2 使用
CAST(... AS NUMBER) 捕捉例外判斷數字,例外處理機制極其昂貴,比逐字元檢核更慢 3 倍。
- 同樣邏輯若改寫為純 SQL(
REGEXP_LIKE、SUBSTR、CASE),可完全避免切換開銷。
優化策略
- 能用單一 SQL 解決,絕不寫函數:將邏輯內聯至查詢或 View。
- 必須用函數時:
- 加上
DETERMINISTIC 關鍵字(相同輸入必得相同輸出),啟用結果快取。
- 使用
PRAGMA UDF (12c+) 減少呼叫開銷。
- 避免函數內執行 SQL、迴圈、例外處理。
- 批次處理改用 FORALL + BULK COLLECT,最小化引擎切換次數。
- 大量資料轉換/驗證:考慮
INSERT /*+ APPEND */ + CTAS 純 SQL 完成,再交換表名。
函數版本 1 核心邏輯(參考)
FUNCTION f_extract_birthday_v1(p_type VARCHAR2, p_no VARCHAR2, p_bday DATE) RETURN NUMBER IS
v_year NUMBER;
BEGIN
IF p_bday IS NOT NULL THEN RETURN EXTRACT(YEAR FROM p_bday); END IF;
IF p_type <> '0' AND p_type <> '1' THEN RETURN NULL; END IF; -- 非身分證
-- 身分證號解析邏輯...
RETURN v_year;
END;
結論:PL/SQL 函數是邏輯封裝利器,但高頻逐行呼叫是效能殺手。架構設計時應優先考慮純 SQL 方案,將函數保留給低頻、複雜、不可 SQL 化的業務邏輯。
copyright ©
萬盛學電腦網 all rights reserved