萬盛學電腦網

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

深入認識 Oracle 表與視圖:從建立管理到分區/臨時/物化視圖進階特性

表是 Oracle 資料儲存的基本結構,視圖是邏輯查詢的封裝。Oracle 8i/9i/10g 持續增強表功能:分區表、物件表、臨時表、外部表。本文系統梳理表與視圖的建立、管理、進階類型與最佳實踐。

基本表建立與管理

CREATE TABLE products (
  prod_id    NUMBER(4) PRIMARY KEY,
  prod_name  VARCHAR2(20) NOT NULL,
  stock_qty  NUMBER(5,3) DEFAULT 0
);

-- 修改表
ALTER TABLE products ADD (category VARCHAR2(15));
ALTER TABLE products MODIFY (prod_name VARCHAR2(30));
ALTER TABLE products DROP COLUMN category;
ALTER TABLE products RENAME COLUMN prod_name TO product_name;

-- 刪除表 (含約束/索引)
DROP TABLE products CASCADE CONSTRAINTS PURGE;  -- PURGE 直接釋放空間,不進回收筒

進階表類型 (8i/9i/10g+)

  • 臨時表 (9i+):CREATE GLOBAL TEMPORARY TABLE temp_session_data ... ON COMMIT PRESERVE/DELETE ROWS。資料隔離於 Session/Transaction,自動清理,無 Redo/Undo 開銷
  • 外部表 (9i+):CREATE TABLE ... ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ...)。直接查詢 OS 平面檔 (CSV、定寬),不載入資料庫
  • 索引組織表 (IOT):CREATE TABLE ... ORGANIZATION INDEX。資料即索引 (B-Tree 葉節點存完整行),適合主鍵查詢為主、無二級索引需求的窄表
  • 叢集表:CLUSTER。多表共享資料塊,Join 效能極高,但插入/更新開銷大
  • 視圖:邏輯層封裝

    -- 簡單視圖
    CREATE VIEW v_active_products AS
    SELECT prod_id, product_name, stock_qty
    FROM products
    WHERE stock_qty > 0;
    
    -- 複雜視圖 (含 JOIN、聚合、GROUP BY) → 唯讀
    CREATE VIEW v_sales_summary AS
    SELECT p.category, SUM(s.qty) total_qty
    FROM products p, sales s
    WHERE p.prod_id = s.prod_id
    GROUP BY p.category;
    
    -- WITH CHECK OPTION:防止 DML 違反視圖 WHERE 條件
    CREATE VIEW v_high_stock AS
    SELECT * FROM products WHERE stock_qty > 100
    WITH CHECK OPTION;
    
    -- 物化視圖 (Materialized View) - 預計算結果,支援快速重新整理/查詢重寫
    CREATE MATERIALIZED VIEW mv_monthly_sales
    BUILD IMMEDIATE REFRESH FAST ON DEMAND
    ENABLE QUERY REWRITE AS
    SELECT trunc(sale_date,'MM') month, SUM(amount) total
    FROM sales GROUP BY trunc(sale_date,'MM');

    視圖與表的關鍵差異

    類型關鍵字/特徵適用場景
    分區表PARTITION BY RANGE/LIST/HASH/INTERVAL超大表、歷史資料滾動、查詢裁剪、維護單一分區
    物件表基於 OBJECT TYPE,支援 REF、嵌套表、VARRAY物件導向應用、空間/多媒體資料
    特性表視圖物化視圖
    儲存資料是否 (僅存查詢定義)是 (預計算結果)
    DML 操作全支援受限 (單表、無聚合、含 CHECK OPTION)需透過重新整理更新
    索引任意建立不可直接建立可建立索引加速查詢
    查詢重寫無無Oracle 自動重寫查詢使用 MV

    最佳實踐建議

    1. **主鍵必建**,外鍵建索引 (避免鎖升級)
    2. 大表**分區 + 本地索引**,配合 INTERVAL 分區自動管理
    3. 中繼運算結果用**物化視圖 + QUERY REWRITE**,透明加速報表
    4. Session 級暫存資料用**全域暫存表** (GTT),無 Redo/Undo、自動清理
    5. 外部檔案定期載入用**外部表 + CTAS/INSERT APPEND**,並行直載
    6. 視圖命名規範:V_ (一般)、MV_ (物化)、V_*_RO (唯讀)

    結語

    表與視圖是 Oracle 物件體系的基石。掌握**分區、臨時、外部、IOT、物化視圖**等進階類型,能在資料量增長、查詢複雜化、維護窗口縮小時,提供優雅的架構解法。選對儲存結構,事半功倍。

    copyright © 萬盛學電腦網 all rights reserved