Oracle 全域暫存表完整實戰指南：類型選擇、建立語法、適用場景與擴展方案

前言：大數據量系統的效能利器

在百萬級以上資料量的 Oracle 系統中，多表關聯查詢常面臨效能挑戰。當關聯大表後結果集極小且查詢極快時，全域暫存表（GTT）是極佳選擇。GTT 不佔用永久表空間，資料存放於系統暫存表空間 (TEMP)，用完即釋放。

兩大類型與建立語法

1. 工作階段級 (Session-level) — ON COMMIT PRESERVE ROWS

• 資料生命週期綁定 Session：Session 不退出資料保留，退出時自動 TRUNCATE
• 不同 Session 互不干擾，各見各的資料
• 適用：跨多個交易/程序單元共享暫存資料

CREATE GLOBAL TEMPORARY TABLE Student (
  Stu_id    NUMBER(5),
  Class_id  NUMBER(5),
  Stu_Name  VARCHAR2(8),
  Stu_Memo  VARCHAR2(200)
) ON COMMIT PRESERVE ROWS;

2. 交易級 (Transaction-level) — ON COMMIT DELETE ROWS

• 資料生命週期綁定交易：COMMIT 或 ROLLBACK 即自動清空
• Session 結束也會自動清空
• 適用：單一交易內的中間運算結果

CREATE GLOBAL TEMPORARY TABLE Classes (
  Class_id   NUMBER(5),
  Class_Name VARCHAR2(8),
  Class_Memo VARCHAR2(200)
) ON COMMIT DELETE ROWS;

核心差異對比

適用場景

1. 多大表關聯產生小結果集：將大表拆解、過濾後存入 GTT，再關聯小表
2. 程序執行過程中需暫存中間資料：整個 Session 期間反覆存取
3. 複雜報表運算：分步驟計算，中間結果暫存 GTT

GTT 限制與自建擴展方案

原生限制

• 不支援 LOB 物件（含 SDO_GEOMETRY 等空間類型）
• 不支援主外鍵約束

自建擴展暫存表設計（彌補限制）

-- 1. 建立一般表，主鍵加入 SessionID
CREATE TABLE my_temp_table (
  session_id NUMBER PRIMARY KEY,  -- 區分不同 Session
  col1       VARCHAR2(100),
  col2       CLOB,                 -- 支援 LOB
  CONSTRAINT fk_ref FOREIGN KEY (ref_id) REFERENCES ref_table(id)  -- 支援外鍵
);

-- 2. 建立登出觸發器，自動清理當前 Session 資料
CREATE OR REPLACE TRIGGER cleanup_temp_on_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
  DELETE FROM my_temp_table WHERE session_id = SYS_CONTEXT('USERENV', 'SID');
END;

-- 3. 應用層寫入/讀取時自動帶入 SessionID
-- INSERT: session_id = SYS_CONTEXT('USERENV', 'SID')
-- SELECT: WHERE session_id = SYS_CONTEXT('USERENV', 'SID')

進階擴展優勢

• 支援 LOB、SDO_GEOMETRY 等複雜類型
• 支援基於 Session 的主外鍵關聯
• 可建立 View 過濾 SessionID，對應用層透明
• 高權限用戶可存取全域資料，支援更複雜業務