Oracle 视图与物化视图详解

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

---

1. 概述

视图和物化视图是 Oracle 重要对象[1]：

详细见：Oracle 物化视图与查询重写。

---

2. 视图

2.1 创建

CREATE OR REPLACE VIEW emp_dept AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 查询
SELECT * FROM emp_dept;

2.2 WITH CHECK OPTION

CREATE OR REPLACE VIEW emp_it AS
SELECT * FROM employees WHERE dept_id = 10
WITH CHECK OPTION CONSTRAINT chk_it;

-- 仅能插入/更新 dept_id=10 的
INSERT INTO emp_it VALUES (..., 10);  -- OK
INSERT INTO emp_it VALUES (..., 20);  -- ERROR

2.3 WITH READ ONLY

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

-- 仅查询，不可 DML

2.4 FORCE / NOFORCE

-- FORCE：强制创建（即使基表不存在）
CREATE FORCE VIEW v_nonexistent AS SELECT * FROM nonexistent_table;

2.5 复杂视图

CREATE OR REPLACE VIEW dept_summary AS
SELECT d.dept_name, COUNT(e.id) AS emp_count, AVG(e.salary) AS avg_salary
FROM departments d, employees e
WHERE d.id = e.dept_id
GROUP BY d.dept_name;

2.6 INSTEAD OF 触发器

CREATE OR REPLACE TRIGGER trg_emp_dept
INSTEAD OF INSERT ON emp_dept
FOR EACH ROW
BEGIN
  INSERT INTO employees (id, name, salary, dept_id)
  VALUES (:NEW.id, :NEW.name, :NEW.salary, ...);
END;
/

详细见：Oracle PL/SQL 触发器详解。

---

3. 物化视图

3.1 创建

CREATE MATERIALIZED VIEW mv_dept_avg
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id;

3.2 刷新方式

ON COMMIT

CREATE MATERIALIZED VIEW mv_emp
  REFRESH FAST ON COMMIT
  AS SELECT * FROM employees;

ON DEMAND

CREATE MATERIALIZED VIEW mv_emp
  REFRESH FAST ON DEMAND
  START WITH SYSDATE NEXT SYSDATE + 1
  AS SELECT * FROM employees;

-- 手动
EXEC DBMS_MVIEW.REFRESH('mv_emp');
EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C');  -- Complete
EXEC DBMS_MVIEW.REFRESH('mv_emp', 'F');  -- Fast
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS;

3.3 刷新类型

类型	说明
COMPLETE	完全刷新
FAST	增量（需物化视图日志）
FORCE	优先 FAST，失败 COMPLETE
NEVER	不刷新

3.4 BUILD

-- BUILD IMMEDIATE（默认）
BUILD IMMEDIATE AS ...

-- BUILD DEFERRED
BUILD DEFERRED AS ...
-- 稍后填充
EXEC DBMS_MVIEW.POPULATE('mv_emp');

---

4. 物化视图日志

4.1 创建

CREATE MATERIALIZED VIEW LOG ON employees
  WITH PRIMARY KEY, ROWID, SEQUENCE
  INCLUDING NEW VALUES;

4.2 列

CREATE MATERIALIZED VIEW LOG ON employees
  WITH PRIMARY KEY, SEQUENCE
  (dept_id, salary)
  INCLUDING NEW VALUES;

4.3 查看

SELECT * FROM mlog$_employees;

---

5. 查询重写

5.1 启用

ALTER SESSION SET query_rewrite_enabled = TRUE;
ALTER SESSION SET query_rewrite_integrity = enforced;
-- enforced / trusted / stale_tolerated

ALTER SYSTEM SET query_rewrite_enabled = TRUE;

5.2 物化视图

CREATE MATERIALIZED VIEW mv_dept_avg
  ENABLE QUERY REWRITE
  AS SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

5.3 验证

EXPLAIN PLAN FOR 
  SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));
-- 应看到 MAT_VIEW REWRITE ACCESS

详细见：Oracle 执行计划详解。

---

6. 类型

6.1 主键

CREATE MATERIALIZED VIEW mv_emp
  REFRESH FAST ON COMMIT
  WITH PRIMARY KEY
  AS SELECT id, name FROM employees;

6.2 ROWID

CREATE MATERIALIZED VIEW mv_emp
  REFRESH FAST WITH ROWID
  AS SELECT * FROM employees;

6.3 复杂

CREATE MATERIALIZED VIEW mv_summary
  REFRESH FAST ON DEMAND
  AS
  SELECT d.dept_name, COUNT(*) AS cnt, SUM(e.salary) AS total
  FROM departments d, employees e
  WHERE d.id = e.dept_id
  GROUP BY d.dept_name;

---

7. 限制

7.1 FAST 刷新

- 需物化视图日志
- 连接限制
- 聚合限制
- UNION ALL 限制

7.2 不可 FAST

- 复杂表达式
- 某些分析函数
- DISTINCT + UNION

7.3 限制验证

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('mv_emp');

SELECT * FROM mv_capabilities_table 
WHERE capability_name LIKE 'REFRESH%';

---

8. 管理

8.1 查看

SELECT mview_name, refresh_method, refresh_mode, last_refresh_type, last_refresh_date
FROM user_mviews;

SELECT name, type, staleness
FROM user_mview_refresh_times;

8.2 修改

ALTER MATERIALIZED VIEW mv_emp 
  REFRESH FAST ON DEMAND
  START WITH SYSDATE NEXT SYSDATE + 1;

ALTER MATERIALIZED VIEW mv_emp COMPILE;

8.3 删除

DROP MATERIALIZED VIEW mv_emp;
DROP MATERIALIZED VIEW LOG ON employees;

---

9. 刷新组

BEGIN
  DBMS_REFRESH.MAKE(
    name => 'refresh_group_1',
    list => 'mv_emp, mv_dept, mv_summary',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1'
  );
END;
/

EXEC DBMS_REFRESH.REFRESH('refresh_group_1');
EXEC DBMS_REFRESH.DESTROY('refresh_group_1');

---

10. 性能

10.1 视图

- 不存储数据
- 查询展开
- 性能等同基表

10.2 物化视图

- 存储数据
- 查询重写
- 刷新开销
- 空间

---

11. 应用场景

11.1 视图

- 简化查询
- 安全（列/行）
- 兼容性
- 抽象

11.2 物化视图

- 汇总
- 数据仓库
- 报表
- 远程数据
- 缓存

详细见：Oracle 数据仓库 ETL。

---

12. 监控

12.1 视图

SELECT view_name, text FROM user_views;

12.2 物化视图

SELECT mview_name, last_refresh_date, staleness 
FROM user_mviews;

12.3 查询重写

SELECT name, value FROM v$parameter WHERE name LIKE '%rewrite%';

---

13. 常见坑与排错

13.1 ORA-12054

- 物化视图不可 FAST
- 检查限制

13.2 ORA-12032

- 物化视图日志
- ROWID 或 PRIMARY KEY

13.3 查询不重写

- ENABLE QUERY REWRITE
- query_rewrite_enabled
- 完整性级别

---

14. 最佳实践

1. 视图：简化
2. 物化视图：性能
3. FAST 刷新：增量
4. 物化视图日志：必要
5. 查询重写：自动
6. 刷新组：一致性
7. BUILD IMMEDIATE：常用
8. ON COMMIT：实时
9. ON DEMAND：定时
10. 监控：及时

---

15. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “Materialized Views” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwh/