Oracle 全域暫存表：加速複雜查詢的利器

當 Oracle 資料庫支撐的應用系統面臨百萬級以上的龐大資料量，且涉及多張大表聯結時，若發現某幾張大表聯結後的結果集極小，且取得該結果集的速度很快，此時引入「全域暫存表」往往能事半功倍。

什麼是全域暫存表？

全域暫存表的定義對所有 Session 可見，但資料具有隔離性：每個 Session 只能看見自己插入的資料。表結構持久存在，資料則隨 Session 或 Transaction 結束自動清除，不佔用永久表空間，預設儲存於 TEMP 表空間，大幅降低 I/O 爭用。

兩種壽命類型

建立範例

-- Session 級暫存表
CREATE GLOBAL TEMPORARY TABLE tmp_student (
  stu_id     NUMBER(5),
  class_id   NUMBER(5),
  stu_name   VARCHAR2(8),
  stu_memo   VARCHAR2(200)
) ON COMMIT PRESERVE ROWS;

-- Transaction 級暫存表
CREATE GLOBAL TEMPORARY TABLE tmp_class (
  class_id   NUMBER(5),
  class_name VARCHAR2(8),
  class_memo VARCHAR2(200)
) ON COMMIT DELETE ROWS;

實務操作示範

以下示範 Session 級暫存表的資料生命週期：

INSERT INTO tmp_student VALUES (1, 1, '張三', '福建');
INSERT INTO tmp_student VALUES (2, 1, '劉德華', '福州');
COMMIT;  -- Session 級：Commit 後資料仍保留
SELECT * FROM tmp_student;  -- 可見 2 筆

INSERT INTO tmp_student VALUES (3, 2, 'S.H.E', '廈門');
ROLLBACK;  -- Session 級：Rollback 只會移回未 Commit 的那一筆
SELECT * FROM tmp_student;  -- 仍可見前 2 筆

效能優化關鍵

• 減少大表掃描：將大表經過高選擇率條件過濾後的小結果集寫入暫存表，後續多次聯結只掃描暫存表。
• 避免重複運算：複雜衍生欄位、聚合計算在暫存表建立時一次完成。
• 統計資訊：Oracle 12c 起支援暫存表 Session 級統計資訊，優化器能產生更精準執行計畫。

善用全域暫存表，能將「大表聯結大表」轉化為「大表過濾→暫存表→小表聯結」，顯著降低邏輯讀與 Temp 空間壓力，是大型報表與 ETL 流程的常用優化手段。