深入解析 Oracle 全域暫存表（Global Temporary Table）機制與應用

Oracle 全域暫存表（Global Temporary Table，GTT）是處理會話級或交易級中間運算結果的利器。與傳統永久表不同，GTT 的**定義持久化、資料隔離、自動清理**三大特性，能大幅降低 Redo/Undo 開銷、簡化應用邏輯。本文系統梳理 GTT 的架構原理、兩種保留模式、建立語法、限制條件與最佳實踐。

核心特性一覽

兩種資料保留模式

1. ON COMMIT PRESERVE ROWS —— 會話級

CREATE GLOBAL TEMPORARY TABLE temp_session (
  empno NUMBER(4),
  ename VARCHAR2(20)
) ON COMMIT PRESERVE ROWS;

• 資料在 會話結束（斷線） 或手動 DELETE/TRUNCATE 前持續存在。
• 跨 COMMIT 仍保留，適合多步驟交易流程共用中間結果。

2. ON COMMIT DELETE ROWS —— 交易級（預設）

CREATE GLOBAL TEMPORARY TABLE temp_txn (
  empno NUMBER(4),
  ename VARCHAR2(20)
) ON COMMIT DELETE ROWS;

• 資料在 每次 COMMIT/ROLLBACK 後自動清空。
• 底層直接釋放暫存段，無逐行刪除開銷，效能極佳。
• 適合單一交易內的暫存計算、報表中間表。

架構原理：延遲段分配與私有暫存段

• 首次 INSERT 才分配段：建立 GTT 時不佔用儲存空間，會話首次寫入資料時，才在該會話的暫存表空間分配私有暫存段。
• 各會話獨立段：不同會話在不同表空間、不同區塊，完全隔離，無鎖衝突。
• 統計資訊挑戰：優化器無法收集各會話真實資料分佈，可能產生非最佳執行計畫（詳見「效能調校」章節）。

建立語法完整範例

CREATE GLOBAL TEMPORARY TABLE gtt_sales_summary (
  region_id    NUMBER(3),
  sales_amt    NUMBER(15,2),
  calc_date    DATE DEFAULT SYSDATE
)
ON COMMIT PRESERVE ROWS  -- 或 DELETE ROWS
-- 可選：指定暫存表空間
-- TABLESPACE temp_tbs
-- 可選：平行度
-- PARALLEL 4;

支援與不支援的物件特性

從 SQL Server / Sybase 遷移的關鍵觀念轉換

錯誤做法： SELECT ... INTO #temp FROM ... 動態建表（執行期 DDL）

正確做法：

1. 部署階段 一次性建立 GTT（如同永久表）
2. 執行期僅 INSERT INTO gtt_temp SELECT ... FROM ...

避免執行期 DDL 帶來的硬解析、鎖爭用、計畫失效等效能殺手。

效能調校與最佳實踐

1. 統計資訊管理（12c+ 推薦）

-- 會話級統計（僅對當前會話生效）
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'GTT_SALES', scope=>'SESSION');

-- 或啟用動態採樣
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 4;

2. 索引策略

• 依查詢模式建立 B-tree 索引，ON COMMIT PRESERVE ROWS 表索引隨資料持久；DELETE ROWS 表索引隨資料清空自動維護。
• 避免過度索引：暫存表資料量通常可控，全表掃描往往足夠。

3. 併發與暫存表空間

• 大量併發會話寫入大量資料時，確保 TEMP 表空間足夠並啟用 AUTOEXTEND。
• 監控 V$TEMPSEG_USAGE 觀察暫存段使用情況。

4. 實戰模式：ETL 階段性暫存

-- 1. 會話級 GTT 保留跨步驟中間結果
CREATE GLOBAL TEMPORARY TABLE gtt_stage_cust ON COMMIT PRESERVE ROWS AS
SELECT * FROM staging_customers WHERE 1=0;

-- 2. 步驟 A：載入原始資料
INSERT INTO gtt_stage_cust SELECT * FROM ext_customers;

-- 3. 步驟 B：清洗轉換
UPDATE gtt_stage_cust SET ...;

-- 4. 步驟 C：寫入目標表
INSERT INTO dw_customers SELECT * FROM gtt_stage_cust;

-- 5. 會話結束自動清理，無需額外維護

常見誤區與避坑指南

• 誤以為 GTT 完全不產生 Redo：實際仍產生 Undo 相關 Redo，大量 DML 仍有 Redo 量，但遠小於永久表。
• 在 PL/SQL 迴圈中反覆建立/刪除 GTT：嚴重效能反模式，應在安裝腳本一次性建立。
• 嘗試建立 FK 指向 GTT：不支援，改用觸發器或應用層邏輯保證一致性。
• 依賴 ANALYZE 收集統計：GTT 不支援，改用 DBMS_STATS 或動態採樣。

善用全域暫存表，能讓複雜的多階段運算、報表暫存、ETL 中間層邏輯變得簡潔、高效且易維護，是 Oracle 開發者工具箱中的必備利器。