Oracle 參數化視圖實戰：Package 變數法 vs 臨時表法，效能實測與結論

痛點：視圖無參數，長查詢需動態過濾

場景：報表視圖 M_VIEW 基於 3 表（A 百行、B 3萬、C 60萬，月增 3萬）。
原視圖：SELECT ... FROM A,B,C WHERE A.NAME=GET_A_NAME(B.ID) AND B.ID=C.ID
需求：報表只查指定月份（C.TIME = :target_month），期望大幅降低掃描量。

方案一：Package 全域變數模擬參數

1. 建立 Package 保存狀態

CREATE OR REPLACE PACKAGE pkg_report AS
  FUNCTION get_time RETURN VARCHAR2;
  PROCEDURE set_time(p_time VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_report AS
  g_time VARCHAR2(6);
  FUNCTION get_time RETURN VARCHAR2 IS BEGIN RETURN g_time; END;
  PROCEDURE set_time(p_time VARCHAR2) IS BEGIN g_time := p_time; END;
END;
/

2. 改寫視圖引用 Package 函數

CREATE OR REPLACE VIEW M_VIEW AS
SELECT COL1, COL2, COL3
FROM A, B, C
WHERE A.NAME = GET_A_NAME(B.ID)
  AND B.ID = C.ID
  AND C.TIME = PKG_REPORT.GET_TIME();

3. 應用調用流程

BEGIN PKG_REPORT.SET_TIME('202310'); END;  -- 設定參數
SELECT * FROM M_VIEW;                       -- 查詢視圖
-- 產出報表...

方案二：Global Temporary Table (GTT) 物化中間結果

1. 建立 Session 級 GTT

CREATE GLOBAL TEMPORARY TABLE M_TEMP_TABLE (
  COL1 VARCHAR2(10),
  COL2 VARCHAR2(80),
  COL3 VARCHAR2(16)
) ON COMMIT PRESERVE ROWS NOCACHE;  -- Session 級，Commit 保留

2. 視圖直接查 GTT

CREATE OR REPLACE VIEW M_VIEW AS
SELECT * FROM M_TEMP_TABLE;

3. 填充程序

CREATE OR REPLACE PROCEDURE PR_DYNAMIC_DATA(
  PS_TIME IN VARCHAR2,
  RTN     OUT NUMBER
) IS
BEGIN
  RTN := 1;
  BEGIN
    INSERT INTO M_TEMP_TABLE
    SELECT COL1, COL2, COL3
    FROM A, B, C
    WHERE A.NAME = GET_A_NAME(B.ID)
      AND B.ID = C.ID
      AND C.TIME = PS_TIME;
  EXCEPTION WHEN OTHERS THEN RTN := -1; END;

  IF RTN = 1 THEN COMMIT; ELSE ROLLBACK; END IF;
END;
/

4. 應用調用流程

DECLARE v_rtn NUMBER; BEGIN
  PR_DYNAMIC_DATA('202310', v_rtn);  -- 填充 GTT
  SELECT * FROM M_VIEW;              -- 查詢視圖（實際查 GTT）
END;

效能實測結果（同環境、同數據量）

深度分析：為什麼 Cost 騙了我們？

1. Predicate Pushing 失效：視圖合併時，PKG_REPORT.GET_TIME() 被視為「非確定性函數」，優化器**不敢下推**到基表存取路徑，仍全掃 C 表再過濾。
2. GTT 統計資訊缺失：Session 級 GTT 無共享統計，優化器採用預設基數（如 100 行），導致計畫偏差（可能選 Nested Loops 而非 Hash Join）。
3. 額外開銷：方案二需額外 INSERT + COMMIT，單次報表反而多一次寫入。

正確優化路徑：動態 SQL / 物化視圖 / 分區表

結論與最佳實踐

1. Oracle 視圖本質不支援參數，Package/GTT 變通均屬 Hack，**無法保證謂詞下推**，效能不可控。
2. 報表類動態過濾，首選應用層動態 SQL（MyBatis ``、JPA Specifications、存儲過程 REF CURSOR）。
3. 若必須在資料庫層封裝，改用 **Pipelined Table Function**：

   CREATE FUNCTION get_report(p_time VARCHAR2) RETURN report_tab_type PIPELINED IS
   BEGIN
     FOR r IN (SELECT ... FROM A,B,C WHERE ... AND C.TIME = p_time) LOOP
       PIPE ROW(r);
     END LOOP;
     RETURN;
   END;
   -- 調用：SELECT * FROM TABLE(get_report('202310'));

   函數輸入參數明確，**優化器可下推**、執行計畫穩定。
4. GTT 適用於**複雜中間結果多次復用**（同 Session 內多報表共用），而非單次參數化過濾。

避坑清單

• 視圖中呼叫 PL/SQL 函數 → **阻礙 Predicate Pushing / Join Elimination / Partition Pruning**。
• GTT 無統計 → **動態採樣 (DYNAMIC_SAMPLING) 或手工 DBMS_STATS.GATHER_TABLE_STATS（需 12c+ 支援 GTT 統計）**。
• Package 變數在 RAC/Shared Server 環境下**非跨實例共享**，若連接池複用 Session 會發生參數殘留 Bug。

結語

參數化視圖是**偽需求**。真正的需求是「同一邏輯、不同參數、高效執行」。Oracle 提供的正規武器是：**Table Function（參數化邏輯封裝）、Materialized View（預計算）、Partition Pruning（物理裁剪）、VPD Context（安全下推）**。拋棄 Package/GTT Hack，擁抱正規兵器，才能獲得穩定、可預測的效能。

1. 上一篇文章： Oracle 透明網關多 SQL Server 實例接入：標準化部署範本與維運清單
2. 下一篇文章： Oracle 進程記憶體結構圖解：PGA、UGA、CGA 分配與釋放全生命週期

Oracle 資料庫基礎

解決 Oracle ORA-01460 錯誤：字符集不相容導致的 PL/SQL Developer 連線問題

ORA-01460 錯誤根因分析與解決方案 當使用 PL/S

Red Hat Linux 9.0 上安裝 Oracle 9.2 完整圖文教學

前言：相容性挑戰 Red Hat 9.0 採用較新版 GCC

Oracle 連接架構概覽：共享服務器與專用服務器連接模式對比

連接模式總覽 圖 2-6 概括了本章所有內容，展示了使用共享

HBase 鬆散資料儲存設計入門：資料模型、架構與寫入流程

什麼是鬆散資料？ 介於 Key-Value 與關聯式資料列之