萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

深入剖析 Oracle 全域暫存表:架構、生命週期與避坑實戰

為什麼要研究暫存表?

許多團隊從 SQL Server/Sybase 遷移至 Oracle,習慣在存儲過程中 `SELECT ... INTO #temp` 動態建表。Oracle **不支援**此模式,若在 PL/SQL 執行期動態 `CREATE GLOBAL TEMPORARY TABLE`,會產生大量 DDL、硬解析、資料字典膨脹,嚴重拖垮效能與穩定性。本文釐清 Oracle 暫存表正確用法。

建立語法與兩種生命週期

CREATE GLOBAL TEMPORARY TABLE tmp_table (
  col1 datatype [DEFAULT expr] [NULL | NOT NULL],
  col2 datatype [DEFAULT expr] [NULL | NOT NULL]
)
ON COMMIT { DELETE | PRESERVE } ROWS;
選項清除時機適用場景
ON COMMIT DELETE ROWS交易 Commit/Rollback 瞬間單一交易內暫存中間結果
ON COMMIT PRESERVE ROWSSession 結束(斷線/登出)跨交易、跨程序共享暫存資料

核心特性

  • 定義永久、資料暫時:建立一次即存在資料字典,所有 Session 共用結構,但彼此資料完全隔離(Session A 看不到 Session B 的資料)。
  • 無鎖衝突:即使 `LOCK TABLE tmp_table`,也不阻擋其他 Session 讀寫自己的暫存資料。
  • 不產生 Redo:資料變更不寫 Redo Log(僅產生 Undo),大幅降低 I/O。
  • 統計資訊共享:12c 前統計資訊為全域共享,12c+ 支援 Session 級私有統計(`DBMS_STATS.SET_TABLE_PREFS(..., 'GLOBAL_TEMP_TABLE_STATS', 'SESSION')`)。

正確使用模式:應用安裝期建表,運行期只寫資料

-- 1. 部署腳本(只跑一次)
CREATE GLOBAL TEMPORARY TABLE gtt_work (
  id       NUMBER,
  val      VARCHAR2(100),
  proc_ts  DATE DEFAULT SYSDATE
) ON COMMIT PRESERVE ROWS;

-- 2. 存儲過程(運行期反覆呼叫)
CREATE OR REPLACE PROCEDURE proc_do_work AS
BEGIN
  -- 直接寫入,無需建表
  INSERT INTO gtt_work (id, val)
  SELECT some_id, some_val FROM source_table WHERE ...;

  -- 後續邏輯直接查 gtt_work
  FOR r IN (SELECT * FROM gtt_work) LOOP
    ...
  END LOOP;

  -- 交易結束自動清空(若 ON COMMIT DELETE ROWS)
  -- 或手動 TRUNCATE/DELETE 釋放空間
END;
/

常見錯誤與後果

錯誤做法後果
存儲過程裡 `EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ...'`每次呼叫產生 DDL → 硬解析暴增、Library Cache 爭用、資料字典膨脹、甚至 ORA-00600
以為 `DROP TABLE` 能釋放空間暫存表段在 Session 結束前不釋放;若 `ON COMMIT PRESERVE ROWS` 需手動 `TRUNCATE`
用永久表模擬暫存(加 `SESSION_ID` 欄位)產生 Redo/Undo、索引維護成本高、需定期清理殘留資料

進階主題:索引、統計、分區

  • 可對 GTT 建立索引(`CREATE INDEX ON gtt_work(id)`),索引同樣為 Session 隔離。
  • 12c+ 建議啟用 Session 級統計,避免共享統計導致執行計畫偏差。
  • 11gR2+ 支援 REFERENCE PARTITIONING 參考分區,但 GTT 本身不支援分區。

遷移對照表(SQL Server → Oracle)

SQL ServerOracle 正確做法
`CREATE TABLE #tmp ...`部署期 `CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT DELETE ROWS`
`INSERT INTO #tmp SELECT ...`運行期 `INSERT INTO gtt SELECT ...`
`SELECT * FROM #tmp``SELECT * FROM gtt`(自動隔離 Session)
存儲過程結束自動清理若 `ON COMMIT DELETE ROWS`,Commit 即清;否則手動 `TRUNCATE`

結語

Oracle 全域暫存表「定義永久、資料隔離、無 Redo、無鎖爭用」的設計,完美解決暫存中間結果需求。**切記:建表動作屬於部署階段,嚴禁放入運行期程式碼**。遵循此原則,即可享受高效能、零維護的暫存表體驗。

copyright © 萬盛學電腦網 all rights reserved