Oracle 全域暫存表 (GTT) 完全指南：定義、特性、動態創建與實戰陷阱

前言：為何需要「只在當下存在」的表？

複雜報表、多階段 ETL、Session 級快取、避免鎖競爭——全域暫存表 (Global Temporary Table, GTT) 為這些場景提供了「資料隨 Session/Transaction 生滅、定義永久保留」的獨特機制。本文系統闡述 GTT 兩種模式、DDL 特性、動態創建模式、統計資訊管理、效能優勢與常見誤區。

核心語法：兩種生命週期模式

-- 1. Transaction-Specific (預設): ON COMMIT DELETE ROWS
CREATE GLOBAL TEMPORARY TABLE gtt_txn (
    id          NUMBER,
    process_id  VARCHAR2(50),
    payload     CLOB
) ON COMMIT DELETE ROWS;

-- 2. Session-Specific: ON COMMIT PRESERVE ROWS
CREATE GLOBAL TEMPORARY TABLE gtt_sess (
    session_id  NUMBER,
    step_name   VARCHAR2(100),
    result_json VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

關鍵特性對比表

動態創建模式：運行時按需建表

場景：報表參數動態決定欄位、多租戶隔離、避免永久物件膨脹。

CREATE OR REPLACE PROCEDURE create_dyn_gtt (
    p_table_name  IN VARCHAR2,
    p_cols        IN SYS.ODCIVARCHAR2LIST,  -- 欄位定義清單
    p_preserve    IN BOOLEAN DEFAULT FALSE  -- TRUE=Session, FALSE=Transaction
) AS
    v_sql VARCHAR2(4000);
    v_cnt NUMBER;
BEGIN
    -- 檢查是否已存在 (同 Session 內不可重複創建同名 GTT)
    SELECT COUNT(*) INTO v_cnt FROM user_tables WHERE table_name = UPPER(p_table_name);
    IF v_cnt = 0 THEN
        v_sql := 'CREATE GLOBAL TEMPORARY TABLE ' || p_table_name || ' (';
        FOR i IN 1..p_cols.COUNT LOOP
            v_sql := v_sql || p_cols(i) || ',';
        END LOOP;
        v_sql := RTRIM(v_sql, ',') || ') ON COMMIT ' || CASE WHEN p_preserve THEN 'PRESERVE ROWS' ELSE 'DELETE ROWS' END;
        EXECUTE IMMEDIATE v_sql;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- ORA-00955: name already used by existing object (跨 Session 並發創建)
        IF SQLCODE != -955 THEN RAISE; END IF;
END;
/

關鍵點：

• GTT 定義在資料字典中永久存在，不可在同一 Session 重複 CREATE (ORA-00955)，需先檢查 USER_TABLES。
• 動態 DDL 會隱性提交 (Implicit Commit)，謹慎用於交易中間。
• 12c+ 支援 PRIVATE TEMPORARY TABLE (ORA$PTT_*)，定義也在 Session 級私有，更適合極度動態場景。

統計資訊管理：12c 前後的分水嶺

11g 避坑：若不同 Session 資料量差異巨大 (Session A 10 行, Session B 100 萬行)，共享統計會導致其中一方計劃極差。解法：鎖定統計 + 動態採樣 (OPTIMIZER_DYNAMIC_SAMPLING=4) 或 Hint DYNAMIC_SAMPLING。

效能優勢：為何比永久中間表快？

1. 零 Redo 生成：DML 不產生 Redo Log (僅 Undo)，極大減少 Log File Sync 等待、Redo 傳輸頻寬 (Data Guard/GoldenGate)。
2. 零鎖競爭：資料完全 Session 隔離，無 Row Lock、TM Lock、TX Lock 競爭。
3. 高水位線 (HWM) 重置：TRUNCATE (隱性於 Commit/Disconnect) 瞬間重置 HWM，後續全掃描極快。
4. Temp 表空間優化：資料儲存於 Temp 表空間，採用本地管理、統一大小 Extent，分配極快。

實戰陷阱與避雷指南

進階用法：GTT + 管線函數 + 並行查詢

-- 1. GTT 儲存並行 Worker 產生的中間結果
CREATE GLOBAL TEMPORARY TABLE gtt_parallel_stage (
    worker_id   NUMBER,
    chunk_id    NUMBER,
    agg_result  NUMBER
) ON COMMIT PRESERVE ROWS;

-- 2. 並行管線函數寫入 GTT
CREATE OR REPLACE FUNCTION process_chunk(p_chunk_id NUMBER) RETURN SYS_REFCURSOR PIPELINED PARALLEL_ENABLE (PARTITION p_chunk_id BY ANY) IS
BEGIN
    -- 複雜計算...
    INSERT INTO gtt_parallel_stage VALUES (DBMS_DEBUG_JDWP.CURRENT_SESSION_ID, p_chunk_id, v_result);
    PIPE ROW(v_result);
END;

-- 3. 主查詢匯總 GTT
SELECT SUM(agg_result) FROM gtt_parallel_stage;

此模式將並行計算中間結果物質化於 GTT，避免多次重算，且無 Redo/鎖開銷。

總結

GTT 是 Oracle 解決「臨時中間結果物質化」的原生利器：定義永久、資料私有、零 Redo、零鎖、HWM 秒重置。掌握兩種模式 (Transaction/Session)、統計資訊演進 (12c+ Session 級)、動態創建限制、Temp 空間容量規劃、連接池清理策略，即可在複雜報表、ETL、高併發業務中釋放永久表的鎖與 Redo 壓力，實現極致效能。