您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 表複製、臨時表與視圖全攻略：從 CTAS 到 Global Temporary Table 進階用法

編輯：Oracle 教學

Oracle 表複製、臨時表與視圖全攻略：從 CTAS 到 Global Temporary Table 進階用法

「建表如建庫」—— 表結構複製、數據遷移、中間結果暫存、邏輯封裝是日常高頻操作。本文系統整理 CREATE TABLE AS SELECT (CTAS)、INSERT SELECT、視圖、全局臨時表 (GTT) 的語法、差異、適用場景與進階實戰經驗。

一、表結構與數據複製：CTAS 變體

需求	SQL	特點
僅複製結構 (無數據)	`CREATE TABLE new_t AS SELECT * FROM old_t WHERE 1=0;`	最快、無數據、保留列定義/類型/默認值，不複製約束/索引/分區/權限
複製結構 + 數據	`CREATE TABLE new_t AS SELECT * FROM old_t;`	一條龍，同樣不複製約束/索引/分區/觸發器/權限
指定列/轉換複製	`CREATE TABLE new_t AS SELECT id, UPPER(name) name, sal*12 ann_sal FROM old_t;`	列投影、表達式計算、類型推導
跨 Schema 複製	`CREATE TABLE scott.new_t AS SELECT * FROM hr.employees;`	需目標 Schema 有 CREATE TABLE 權限、源表 SELECT 權限

關鍵補充： CTAS 後**必須手動添加**主鍵、外鍵、索引、分區、註釋、權限。可用 DBMS_METADATA.GET_DDL 導出源表完整 DDL 再執行。

二、數據追加/同步：INSERT SELECT


-- 全量追加
INSERT INTO target_t SELECT * FROM source_t;

-- 增量同步 (MERGE 更優)
MERGE INTO target_t t
USING (SELECT * FROM source_t WHERE last_mod > :last_sync) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.col = s.col
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.col...);

三、視圖：邏輯封裝零存儲


-- 創建/替換視圖
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- 刪除視圖
DROP VIEW v_emp_dept;

-- 可更新視圖條件：單表、無聚合/去重/分析函數、包含所有 NOT NULL 列
-- WITH CHECK OPTION 防止 DML 導致行「消失」出視圖範圍
CREATE VIEW v_high_sal AS SELECT * FROM emp WHERE sal > 5000 WITH CHECK OPTION;

四、全局臨時表 (GTT) — 會話/事務級私有數據倉儲

核心特性

定義持久、數據私有： 結構對所有會話可見，數據僅當前會話/事務可見。
無 Redo/Undo 開銷 (數據操作)： DML 不產生 Redo (間接減少 Undo)，極快，適合大量中間結果暫存。
自動清理： 會話結束/事務提交自動 TRUNCATE，無需手動清理。
統計信息共享陷阱： 默認共享統計信息 (12c 前)，導致執行計劃偏移。12c+ 設置 GLOBAL_TEMP_TABLE_STATS = SESSION 或收集會話級統計信息。

兩大類型語法

類型	ON COMMIT 子句	數據生命周期	適用場景
會話級	`ON COMMIT PRESERVE ROWS`	會話結束時清空	多步驟業務流程中間表、報表多輪查詢暫存
事務級	`ON COMMIT DELETE ROWS`	事務提交/回滾時清空	單事務內臨時計算、ETL 階段緩衝

創建示例


-- 會話級：存儲用戶購物車暫時數據
CREATE GLOBAL TEMPORARY TABLE gtt_cart (
  session_id  NUMBER,
  product_id  NUMBER,
  qty         NUMBER,
  add_time    TIMESTAMP DEFAULT SYSTIMESTAMP
) ON COMMIT PRESERVE ROWS;

-- 事務級：批次處理中間聚合
CREATE GLOBAL TEMPORARY TABLE gtt_batch_agg (
  batch_id    NUMBER,
  dept_id     NUMBER,
  total_amt   NUMBER
) ON COMMIT DELETE ROWS;

進階實戰經驗 (血淚總結)

不建索引： GTT 無法創建持久索引 (12c+ 可創建私有索引但罕用)。數據量大、多次掃描 → **性能災難**。大數據量首選 CTAS 普通表 + 事務結束手動 TRUNCATE 或 WITH 子查詢/內聯視圖 推導。
不支持 LOB： 無法定義 CLOB/BLOB 列，需改用普通表。
不支持外鍵： 無法定義 FK 引用或被引用。
統計信息策略： 12c 前 DBMS_STATS.GATHER_TABLE_STATS(..., GATHER_TEMP => TRUE) 收集會話級統計信息；12c+ ALTER TABLE gtt SET GLOBAL_TEMP_TABLE_STATS = SESSION;。
並行 DML 加速： ALTER SESSION ENABLE PARALLEL DML; INSERT /*+ PARALLEL(4) */ INTO gtt ... 可利用並行寫入。

自定義「增強版臨時表」模式 (突破 GTT 限制)：


    -- 普通表 + SESSION_ID 列 + 登出觸發器清理
    CREATE TABLE temp_enhanced (
      session_id  NUMBER DEFAULT SYS_CONTEXT('USERENV','SID'),
      business_col ...
    );
    CREATE INDEX idx_temp_session ON temp_enhanced(session_id);
    
    -- 登出觸發器 (需 DBA 權限)
    CREATE OR REPLACE TRIGGER trg_cleanup_temp
    AFTER LOGOFF ON DATABASE
    BEGIN
      DELETE FROM temp_enhanced WHERE session_id = SYS_CONTEXT('USERENV','SID');
    END;

支持 LOB、索引、外鍵、分區，權限隔離靈活，代價是需維護清理邏輯。

五、選型決策樹

需要持久化數據？
  ├─ 是 → 普通表 (CTAS/INSERT) + 完整約束/索引
  └─ 否 → 數據量大 / 需索引 / 需 LOB / 需 FK？
          ├─ 是 → 普通表 + 手動 TRUNCATE / 分區表按範圍 DROP PARTITION
          └─ 否 → 多事務/多步驟共享？
                  ├─ 是 → GTT ON COMMIT PRESERVE ROWS (會話級)
                  └─ 否 → 單事務內臨時？
                          ├─ 是 → GTT ON COMMIT DELETE ROWS (事務級) / WITH 子查詢
                          └─ 否 → 純邏輯封裝、零存儲 → 視圖

掌握這套「從 CTAS 到增強版臨時表」的完整光譜，讓你在數據暫存、中間計算、邏輯封裝、跨環境遷移等場景中，總能選出「最經濟、最穩健、最易維護」的技術方案。