Oracle SQL 效能優化系列 (八)：以 PL/SQL 函數封裝查詢邏輯提升效能

複雜 SQL 常為了「單一語句」犧牲效能。將重複查詢邏輯封裝為 PL/SQL 函數，主查詢呼叫函數，可大幅減少連接與掃描開銷。

場景：多表聯結查詢員工歷史紀錄

-- 原始：三表聯結 + GROUP BY
SELECT h.empno, e.ename, h.hist_type, t.type_desc, COUNT(*)
FROM history_type t, emp e, emp_history h
WHERE h.empno = e.empno
  AND h.hist_type = t.hist_type
GROUP BY h.empno, e.ename, h.hist_type, t.type_desc;

優化：建立查詢函數

CREATE OR REPLACE FUNCTION lookup_hist_type (typ IN NUMBER) RETURN VARCHAR2 AS
  tdesc VARCHAR2(30);
  CURSOR c1 IS SELECT type_desc FROM history_type WHERE hist_type = typ;
BEGIN
  OPEN c1; FETCH c1 INTO tdesc; CLOSE c1;
  RETURN NVL(tdesc, '?');
END;
/

CREATE OR REPLACE FUNCTION lookup_emp (empno IN NUMBER) RETURN VARCHAR2 AS
  ename VARCHAR2(30);
  CURSOR c1 IS SELECT ename FROM emp WHERE empno = empno;
BEGIN
  OPEN c1; FETCH c1 INTO ename; CLOSE c1;
  RETURN NVL(ename, '?');
END;
/

-- 主查詢簡化為單表掃描 + 函數呼叫
SELECT h.empno, lookup_emp(h.empno),
       h.hist_type, lookup_hist_type(h.hist_type),
       COUNT(*)
FROM emp_history h
GROUP BY h.empno, h.hist_type;

效能增益分析

• 原查詢：三表 Hash Join + Group By，臨時段壓力大。
• 新查詢：單表全掃描 + 兩個函數呼叫 (各自索引查詢 history_type/emp 主鍵)，無連接、無排序。
• 函數結果可被 RESULT_CACHE (11g+) 或應用層快取進一步加速。

適用原則

• 「能否用一條 SQL 寫出...」是常見錯誤導向；可讀性與效能平衡才是關鍵。
• 適用於：維度表查詢、編碼轉換、複雜業務規則計算。
• 不適用於：需推導至執行計畫的關聯過濾 (如動態分區剪枝)。

善用 PL/SQL 函數封裝查詢邏輯，是實戰中兼顧 SQL 簡潔與執行效能的利器。