Oracle View 視圖進階實戰：從建立到 DML、重編譯、Key-Preserved 與效能優化

建立視圖完整語法與子句解析

CREATE [OR REPLACE] [FORCE] VIEW [schema.]view_name
  [(col_alias1, col_alias2, ...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constr_name]]
[WITH READ ONLY];

四大視圖類型 DML 能力對比

Join View 可更新性核心：Key-Preserved Table

**定義**：基表的 PK/UK 在 View 中**仍保持唯一** → Key-Preserved。
鐵律：

1. **只能修改 Key-Preserved 表的欄位**。
2. **單次 DML 只能針對一個基表**。
3. WITH CHECK OPTION 時：Join 條件欄位、基表其他共同欄位**不可更新**；非共同欄位可更新。
4. DELETE 允許（即使有 CHECK OPTION）。
5. Self-Join View：**所有欄位可更新**（邏輯上同一表）。

實例：EMP-DEPT Join View

CREATE VIEW V_EMP_DEPT AS
SELECT e.EMPNO, e.ENAME, e.SAL, d.DNAME, d.LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO;

• EMP 為 Key-Preserved（EMPNO 在 View 中唯一），DEPT 非（DNAME 可能重複）。
• 可更新：ENAME, SAL（EMP 欄位）。
• 不可更新：DNAME, LOC, DEPTNO（Join 欄位/非 Key-Preserved）。
• INSERT 需提供 EMP 所有 NOT NULL 欄位。

Force View 實戰：解耦建立順序

CREATE FORCE VIEW V_FUTURE AS SELECT c1, c2 FROM NOT_YET_EXIST;  -- WARNING: INVALID
CREATE TABLE NOT_YET_EXIST (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(20));
SELECT * FROM V_FUTURE;  -- 自動編譯 → VALID

適用：大型專案並行開發、遷移腳本順序不確定。

視圖維護操作與相依影響

部署最佳實踐：CREATE OR REPLACE VIEW ... 後緊跟批量重編譯：

BEGIN
  FOR r IN (SELECT object_name, object_type
            FROM USER_OBJECTS
            WHERE STATUS = 'INVALID'
              AND OBJECT_TYPE IN ('VIEW','PROCEDURE','FUNCTION','PACKAGE','TRIGGER'))
  LOOP
    EXECUTE IMMEDIATE 'ALTER ' || r.OBJECT_TYPE || ' ' || r.OBJECT_NAME || ' COMPILE';
  END LOOP;
END;
/

查詢視圖中繼資料與可更新欄位

-- 定義文本
SELECT view_name, text FROM USER_VIEWS;

-- 欄位資訊
SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'V_EMP_DEPT';

-- 可更新欄位（關鍵！）
SELECT column_name, insertable, updatable, deletable
FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME = 'V_EMP_DEPT';

視圖合併與效能優化

Oracle 執行查詢視圖時，將**視圖定義與外層查詢合併**為單一查詢塊（Query Transformation），再優化。等同直接寫合併後的 SQL。

阻礙合併的因素（導致次佳計畫）

• Complex View（含 GROUP BY、DISTINCT、集合運算、非合併子查詢）。
• WITH CHECK OPTION / WITH READ ONLY。
• 外層查詢使用 ROWNUM、MODEL、XML 等特殊子句。
• 參數 _OPTIMIZER_VIEW_MERGING = FALSE（隱藏參數，勿動）。

優化建議

1. **Simple/Join View 通常自動合併**，無需干預；確保基表統計資訊最新。
2. **Complex View 改用 Materialized View**：預計算聚合、大表 Join，定時 FAST REFRESH。
3. **避免視圖疊加視圖**超過 3 層；改用 WITH 子句 (CTE) 或拆分邏輯。
4. **安全性視圖**：敏感欄位建立 WITH CHECK OPTION View 授權應用帳號，基表僅 DBA 可見。
5. **命名規範**：V_ + 業務域 + 用途（如 V_HR_EMP_SALARY_MASKED）。

常見陷阱與對策

結語

View 是**邏輯層封裝**利器：Simple View 給安全與簡化，Join View 給多表整合（**Key-Preserved 是靈魂**），Complex View 給報表聚合（不可 DML、慎用高頻）。掌握 OR REPLACE 保留授權、FORCE 解耦順序、WITH CHECK OPTION 守護完整性、MATERIALIZED VIEW 解決效能瓶頸、USER_UPDATABLE_COLUMNS 精準判斷 DML 能力，即可駕馭 View 全生命週期。**記住：視圖不存資料，只存邏輯；合併後的計畫才是真相。**