Oracle 视图（View）与物化视图（Materialized View）

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

类型	说明
视图（View）	虚拟表，存储 SQL
物化视图（MV）	实际表，存储数据

---

2. 视图

2.1 创建

CREATE OR REPLACE VIEW emp_dept_view AS
SELECT 
  e.employee_id,
  e.last_name,
  e.salary,
  d.dept_name,
  d.location
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 使用
SELECT * FROM emp_dept_view WHERE salary > 5000;

2.2 WITH CHECK OPTION

-- 修改数据必须满足视图条件
CREATE OR REPLACE VIEW high_salary_emp AS
SELECT * FROM employees WHERE salary > 5000
WITH CHECK OPTION;

-- 错误：插入 salary <= 5000
INSERT INTO high_salary_emp VALUES (..., 4000);  -- 报错

2.3 WITH READ ONLY

CREATE OR REPLACE VIEW emp_read_only AS
SELECT * FROM employees
WITH READ ONLY;

-- 不能 DML
UPDATE emp_read_only SET salary = 1000;  -- 报错

2.4 FORCE / NOFORCE

-- FORCE：表不存在也创建
CREATE FORCE VIEW emp_view AS
SELECT * FROM non_existent_table;

-- NOFORCE（默认）：表不存在则失败

2.5 修改视图

CREATE OR REPLACE VIEW emp_view AS
SELECT * FROM employees WHERE dept_id = 10;

2.6 删除

DROP VIEW emp_view;

---

3. 视图分类

3.1 简单视图

-- 单表，无函数
CREATE VIEW simple_view AS
SELECT employee_id, last_name FROM employees;

3.2 复杂视图

-- 多表/函数/分组
CREATE VIEW dept_stats AS
SELECT 
  dept_id,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary,
  SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id;

3.3 内联视图

-- FROM 子句中的子查询
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

3.4 对象视图

-- 面向对象
CREATE TYPE person_type AS OBJECT (
  id NUMBER,
  name VARCHAR2(100)
);
/

CREATE VIEW person_view OF person_type WITH OBJECT IDENTIFIER (id) AS
SELECT employee_id AS id, last_name AS name FROM employees;

---

4. 视图 DML 规则

4.1 可更新视图

-- 简单视图可 DML
CREATE VIEW simple_emp AS
SELECT employee_id, last_name, salary FROM employees;

INSERT INTO simple_emp VALUES (1, 'Alice', 5000);  -- 可以
UPDATE simple_emp SET salary = 6000 WHERE employee_id = 1;  -- 可以
DELETE FROM simple_emp WHERE employee_id = 1;  -- 可以

4.2 不可更新情况

• 多表连接（仅可更新键保留表）
• 聚合函数
• GROUP BY
• DISTINCT
• 集合操作
• 表达式列

4.3 INSTEAD OF 触发器

-- 复杂视图 DML
CREATE OR REPLACE TRIGGER trg_emp_dept_view
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (employee_id, last_name, dept_id)
  VALUES (:NEW.employee_id, :NEW.last_name, :NEW.dept_id);
END;
/

---

5. 物化视图

5.1 创建

CREATE MATERIALIZED VIEW mv_emp_dept
BUILD IMMEDIATE  -- 立即构建
REFRESH COMPLETE ON DEMAND  -- 完全刷新，按需
ENABLE QUERY REWRITE  -- 查询重写
AS
SELECT 
  dept_id,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

5.2 刷新方式

方式	说明
COMPLETE	完全刷新（重新计算）
FAST	增量刷新（仅变化）
FORCE	优先 FAST，否则 COMPLETE
NEVER	不刷新

5.3 刷新时机

时机	说明
ON COMMIT	提交时刷新
ON DEMAND	按需刷新
ON SCHEDULE	定时刷新

5.4 刷新示例

-- 手动刷新
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept', 'C');  -- Complete
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept', 'F');  -- Fast
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept', '?');  -- Force

-- 全部刷新
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS;

5.5 FAST 刷新要求

-- 必须有物化视图日志
CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY, ROWID, SEQUENCE
INCLUDING NEW VALUES;

---

6. 物化视图类型

6.1 聚合 MV

CREATE MATERIALIZED VIEW mv_dept_stats
REFRESH FAST ON COMMIT
AS
SELECT 
  dept_id,
  COUNT(*) AS cnt,
  SUM(salary) AS total,
  AVG(salary) AS avg
FROM employees
GROUP BY dept_id;

6.2 连接 MV

CREATE MATERIALIZED VIEW mv_emp_dept
REFRESH FAST ON DEMAND
AS
SELECT 
  e.employee_id,
  e.last_name,
  d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

6.3 嵌套 MV

-- MV 上的 MV
CREATE MATERIALIZED VIEW mv_dept_summary
REFRESH FAST ON DEMAND
AS
SELECT dept_id, SUM(total) AS grand_total
FROM mv_dept_stats
GROUP BY dept_id;

---

7. 查询重写

7.1 启用

-- 会话级
ALTER SESSION SET query_rewrite_enabled = TRUE;
ALTER SESSION SET query_rewrite_integrity = enforced;

-- MV 创建时
CREATE MATERIALIZED VIEW mv_emp
ENABLE QUERY REWRITE
AS SELECT ...;

7.2 重写示例

-- 原始查询
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- 自动重写为
SELECT dept_id, avg_salary FROM mv_dept_stats;

7.3 完整性

级别	说明
ENFORCED	严格（默认）
TRUSTED	信任约束
STALE_TOLERATED	容忍过期

---

8. PREBUILD 表

-- 先建表
CREATE TABLE prebuilt_mv (
  dept_id NUMBER,
  emp_count NUMBER,
  avg_salary NUMBER
);

-- MV 使用现成表
CREATE MATERIALIZED VIEW mv_dept
ON PREBUILT TABLE
AS
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees GROUP BY dept_id;

---

9. MV 管理

9.1 查看

SELECT name, type, refresh_method, refresh_mode, last_refresh
FROM user_mviews;

9.2 删除

DROP MATERIALIZED VIEW mv_emp_dept;

9.3 重编译

ALTER MATERIALIZED VIEW mv_emp_dept COMPILE;

---

10. 视图 vs 物化视图

维度	视图	物化视图
存储	仅 SQL	实际数据
性能	实时查询	预计算
实时性	实时	可能延迟
更新	实时	刷新
空间	无	占用
适用	简化查询	性能优化

---

11. 常见坑与排错

11.1 ORA-01031: 权限不足

-- 创建视图需要 CREATE VIEW 权限
GRANT CREATE VIEW TO user;

-- 物化视图需要 CREATE MATERIALIZED VIEW
GRANT CREATE MATERIALIZED VIEW TO user;

11.2 ORA-12054: 无法刷新

-- FAST 刷新不满足条件
-- 1. 检查物化视图日志
-- 2. 使用 DBMS_MVIEW.EXPLAIN_MVIEW 分析

11.3 查询重写不生效

-- 1. 检查参数
SHOW PARAMETER query_rewrite

-- 2. MV 启用重写
-- 3. 检查完整性级别
-- 4. 使用 DBMS_MVIEW.EXPLAIN_REWRITE

---

12. 最佳实践

1. 视图简化查询：易维护
2. 视图封装权限：安全
3. 复杂聚合用 MV：性能
4. FAST 刷新加日志：增量
5. 合理刷新策略：平衡实时性
6. 查询重写提性能：自动优化
7. WITH CHECK OPTION：数据完整性
8. WITH READ ONLY：安全
9. 避免视图嵌套：性能
10. 定期刷新 MV：数据新鲜

---

13. 参考资料

[1] Oracle Database SQL Language Reference 19c, “CREATE VIEW” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-VIEW.html

[2] Oracle Database Data Warehousing Guide 19c, “Materialized Views” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/materialized-views.html