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

Oracle 建表導資料兩大核心語句：CTAS 與 INSERT SELECT

編輯：Oracle 資料庫基礎

語句一：CREATE TABLE AS SELECT (CTAS) — 一次建表並導入資料


CREATE TABLE vincent_sms AS SELECT * FROM vincent_voice;

特性

**結構複製**：新表欄位名稱、資料型別、長度、精度與來源查詢結果完全一致
**資料複製**：來源查詢返回的所有行直接寫入新表
**不複製約束**：主鍵、外鍵、唯一、檢查、非空等約束**不會**複製
**不複製索引/權限/觸發器/統計資訊**
**支援 WHERE 條件**：`CREATE TABLE t1 AS SELECT * FROM t2 WHERE status = 'A';`
**支援複雜查詢**：JOIN、聚合、子查詢等任意 SELECT 語句

適用場景

快速建立測試/開發環境資料副本
建立資料市集/摘要表
表結構變更時的中間表建立
跨表空間移動表 (搭配 `TABLESPACE` 子句)

進階用法：指定表空間與儲存屬性


CREATE TABLE vincent_sms 
TABLESPACE users_data  -- 指定表空間
PCTFREE 20 
STORAGE (INITIAL 10M NEXT 10M)
AS SELECT * FROM vincent_voice WHERE create_date > SYSDATE - 30;

語句二：INSERT INTO ... SELECT — 向既有表追加資料


INSERT INTO vincent_sms SELECT * FROM vincent_voice;

特性

**目標表必須已存在**
**欄位對應**：預設按 SELECT 列表順序與目標表定義順序一一對應
**建議明確列出欄位**：`INSERT INTO t1 (c1, c2) SELECT c2, c1 FROM t2;` (順序可不同)
**支援 WHERE 條件**
**無 VALUES 關鍵字** (這是與單行 INSERT 最大語法差異)
**觸發器行為**：目標表的 AFTER INSERT 行級觸發器**會逐行觸發**
**約束檢查**：非空、唯一、外鍵等約束**逐行檢查**，違反即回滾整個語句

適用場景

定期同步資料至匯總表/歷史表
ETL 流程中載入維度/事實表
資料遷移/歸檔
測試資料製造

核心差異對照表

維度	CTAS (CREATE TABLE AS SELECT)	INSERT SELECT
目標表	不存在，自動建立	必須已存在
結構來源	SELECT 查詢結果元資料	既有表定義
約束複製	❌ 不複製	✅ 目標表既有約束生效
索引複製	❌ 不複製	❌ 不相關
執行速度	極快 (直接路徑載入，無 Redo 選項)	較慢 (產生 Redo/Undo、觸發器、約束檢查)
並行化	✅ `PARALLEL` 子句原生支援	✅ `/+ APPEND PARALLEL /` Hint
回滾段壓力	極小 (可 `NOLOGGING`)	大 (完整 Redo/Undo)
適用資料量	大量初始載入	增量/中等量追加

效能優化關鍵技巧

CTAS 加速大表建立


-- 1. 並行建立
CREATE TABLE big_table_new PARALLEL 8 
AS SELECT /*+ PARALLEL(s 8) */ * FROM big_table_source s;

-- 2. 無日誌模式 (需資料庫 ARCHIVELOG 模式下謹慎使用)
CREATE TABLE big_table_new NOLOGGING 
AS SELECT * FROM big_table_source;

-- 3. 結合壓縮
CREATE TABLE big_table_new COMPRESS FOR QUERY HIGH
AS SELECT * FROM big_table_source;

INSERT SELECT 加速追加


-- 1. 直接路徑載入 (APPEND) — 繞過緩衝區、直接寫入新區塊、最小化 Redo
INSERT /*+ APPEND */ INTO target_table SELECT * FROM source_table;

-- 2. 並行 DML (需啟用)
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(t 8) */ INTO target_table t SELECT * FROM source_table;

-- 3. 無日誌 (配合 APPEND)
ALTER TABLE target_table NOLOGGING;
INSERT /*+ APPEND */ INTO target_table SELECT * FROM source_table;
-- 完成後記得 ALTER TABLE target_table LOGGING;

常見陷阱與避坑

陷阱	現象	解決
CTAS 不複製主鍵	新表無主鍵，後續 JOIN/更新效能差	建表後立即 `ALTER TABLE ... ADD PRIMARY KEY (...)`
INSERT SELECT 欄位順序錯位	資料寫入錯誤欄位	永遠明確列出目標欄位：`INSERT INTO t (c1,c2) SELECT c2,c1 FROM s`
大量 INSERT 觸發器拖慢	逐行觸發 AFTER INSERT 觸發器	載入前 `ALTER TRIGGER ... DISABLE`，後 `ENABLE`
NOLOGGING 非預期資料遺失	載入後備份前資料庫損壞，無法恢復該段資料	僅用於可重載資料 (如暫存表、測試環境)，生產核心表禁用
PARALLEL 導致資源耗盡	並行度過高佔滿 CPU/IO	並行度 ≤ CPU 核心數，監控 `V$PX_SESSION`

進階：MERGE 語句 — 同步更新與插入 (UPSERT)


MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.col1 = s.col1, t.col2 = s.col2, t.update_time = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (id, col1, col2, create_time) VALUES (s.id, s.col1, s.col2, SYSDATE);

適用於：增量同步、資料倉庫維度表 SCD Type 1 維護。

CTAS 是「建表+導資料」的核武器，INSERT SELECT 是「增量追加」的主力。掌握 PARALLEL、APPEND、NOLOGGING 三大加速鍵，並明確欄位列表、管理觸發器/約束影響，是高效資料操作的核心技能。