前言：為什麼需要暫存表？
在億級資料量的系統中，多表關聯查詢常面臨效能瓶頸。當大表關聯後結果集極小且產出快時，將中間結果集物化到暫存表，再以小表驅動大表，能獲得數量級的效能提升。Oracle Global Temporary Table (GTT) 專為此類「暫時性、會話隔離」資料設計，資料存放於臨時表空間（TEMP），不產生 Redo Log（僅 Undo），大幅降低 I/O 與爭用。
兩種 GTT 類型與生命週期
| 類型 | 建立語法關鍵字 | 資料保留時機 | 適用場景 |
|---|
| 會話級 | ON COMMIT PRESERVE ROWS | Session 結束（斷開連線）時 TRUNCATE | 跨多個事務共享中間結果、報表暫存、ETL 階段資料 |
| 事務級 | ON COMMIT DELETE ROWS | 事務 COMMIT 或 ROLLBACK 時 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;
事務級暫存表
CREATE GLOBAL TEMPORARY TABLE Classes (
Class_id NUMBER(5),
Class_Name VARCHAR2(8),
Class_Memo VARCHAR2(200)
) ON COMMIT DELETE ROWS;
核心特性總覽
- 定義永久、資料暫存： DDL 持久化於資料字典，資料僅存在於當前 Session/Transaction。
- 會話隔離： Session A 完全看不見 Session B 的資料，無鎖競爭。
- 空間高效： 資料寫入 TEMP 表空間，不佔用永久表空間；不產生 Redo（僅 Undo），插入極快。
- 索引/統計資訊： 可建立索引（同樣為暫存），12c 起支援 Session 級統計資訊收集。
- 備份/恢復： 不納入 RMAN 備份，Data Pump 匯出僅匯出結構。
四大典型適用場景
- 大表關聯拆解： 將大表過濾後的小結果集 INSERT INTO GTT，再與其他表 Join。
- 複雜報表/ETL 中間層： 多步驟轉換的暫存結果，跨 Procedure/Job 步驟共享（會話級）。
- 遞迴/階層式查詢暫存： 事務級 GTT 儲存遞迴中間層級資料。
- 應用層暫存用戶選單/購物車： Web Session 對應 DB Session 時極合適。
GTT 限制與不足
- ❌ 不支援 LOB 型別（CLOB/BLOB/NCLOB）
- ❌ 不支援外鍵約束（Referential Integrity）
- ❌ 不支援分區表
- ❌ 統計資訊共享問題（11g 前）： 所有 Session 共用一組統計資訊，可能導致執行計劃偏差（12c 解決）
擴展替代方案：自訂「會話隔離表」突破限制
若專案強制需求 LOB、外鍵或分區，可採用「實體表 + Session ID 欄位 + 觸發器/視圖」模式模擬 GTT：
設計模式
- 表結構： 實體表 +
SESSION_ID NUMBER 列入主鍵/分區鍵。
- 資料寫入： 應用層或 Trigger 自動填入 `SYS_CONTEXT('USERENV','SID')`。
- 資料清理： Logoff Trigger 刪除該 Session ID 資料；或排程作業清理過期 Session。
- 資料讀取： 建立 View 固定條件 `WHERE SESSION_ID = SYS_CONTEXT('USERENV','SID')`，應用層只查 View。
- 進階： 高權限角色可查全域資料（移除 View 過濾條件）。
優勢對比
| 特性 | 原生 GTT | 自訂會話隔離表 |
|---|
| LOB 支援 | ❌ | ✅ |
| 外鍵支援 | ❌ | ✅ |
| 分區支援 | ❌ | ✅ |
| 空間管理 | 自動 (TEMP) | 需規劃永久表空間 + 清理策略 |
| Redo 產生 | 極少 (僅 Undo) | 完整 Redo/Undo |
| 效能 | 極高 | 略低（含 Redo/鎖） |
最佳實踐建議
- 優先使用原生 GTT： 無 LOB/外鍵需求時，效能最優、維護最簡。
- 統計資訊： 12c+ 使用 `DBMS_STATS.GATHER_TABLE_STATS(..., SCOPE => 'SESSION')`；舊版本考慮動態採樣或鎖定統計資訊。
- 索引： GTT 索引同樣為暫存，大量資料載入後建索引效能更好。
- 避免長連線累積資料： 會話級 GTT 若連線池不斷連線，資料會累積直到 Session 釋放，需注意 TEMP 表空間容量規劃。
- 命名規範： 建議前綴 `GTT_` 或 `TMP_` 區分業務永久表。
總結
Oracle GTT 是處理「暫時性、會話隔離、中間結果集」的利器。深入理解 `ON COMMIT PRESERVE/DELETE ROWS` 的語義差異、掌握 LOB/外鍵限制與自訂替代方案，能在複雜報表、ETL、高併發應用中遊刃有餘。選型原則：能用 GTT 就用 GTT，不夠用再自建。
