為什麼要研究暫存表?
許多團隊從 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 ROWS | Session 結束(斷線/登出) | 跨交易、跨程序共享暫存資料 |
核心特性
- 定義永久、資料暫時:建立一次即存在資料字典,所有 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 Server | Oracle 正確做法 |
| `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、無鎖爭用」的設計,完美解決暫存中間結果需求。**切記:建表動作屬於部署階段,嚴禁放入運行期程式碼**。遵循此原則,即可享受高效能、零維護的暫存表體驗。