Oracle 视图与物化视图

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

1. 概述

视图与物化视图[1]：

视图：逻辑，不存储 物化视图：物理，存储

2. 普通视图

2.1 创建

CREATE VIEW v_emp_dept AS
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

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

2.2 WITH CHECK OPTION

CREATE VIEW v_emp_dept10 AS
SELECT * FROM employees WHERE dept_id = 10
WITH CHECK OPTION;

-- 只能插入/修改 dept_id = 10

2.3 WITH READ ONLY

CREATE VIEW v_emp_read AS
SELECT * FROM employees
WITH READ ONLY;

3. 视图查询

3.1 简单

SELECT * FROM v_emp_dept WHERE dept_name = 'IT';

3.2 视图合并

-- 简单视图可合并
SELECT * FROM v_emp_dept WHERE id = 100;
-- 等价
SELECT ... FROM employees e, departments d WHERE ... AND e.id = 100;

3.3 复杂视图

-- 分组视图不可合并
CREATE VIEW v_dept_avg AS
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees GROUP BY dept_id;

4. 内联视图

SELECT e.name, d.avg_sal
FROM employees e,
  (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d
WHERE e.dept_id = d.dept_id;

5. 物化视图

5.1 创建

CREATE MATERIALIZED VIEW mv_emp_dept
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
  AS
  SELECT e.id, e.name, d.dept_name
  FROM employees e, departments d
  WHERE e.dept_id = d.id;

5.2 刷新方式

-- COMPLETE：完全
REFRESH COMPLETE ON DEMAND

-- FAST：增量
REFRESH FAST ON DEMAND

-- FORCE：优先 FAST
REFRESH FORCE ON DEMAND

5.3 刷新时机

-- ON DEMAND：手动
ON DEMAND

-- ON COMMIT：提交时
ON COMMIT

-- ON SCHEDULE：定时
START WITH SYSDATE NEXT SYSDATE + 1

5.4 BUILD

-- IMMEDIATE：立即构建
BUILD IMMEDIATE

-- DEFERRED：延迟
BUILD DEFERRED

6. FAST 刷新

6.1 要求

- 物化视图日志
- 满足 FAST 刷新条件

6.2 日志

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

6.3 刷新

-- 手动
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept', 'F');

-- 完全
EXEC DBMS_MVIEW.REFRESH('mv_emp_dept', 'C');

-- 全部
EXEC DBMS_MVIEW.REFRESH_ALL;

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

7. 查询重写

7.1 启用

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

7.2 参数

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

7.3 验证

EXPLAIN PLAN FOR 
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 应使用 mv_dept_avg

8. 物化视图类型

8.1 主键

-- 默认
WITH PRIMARY KEY

8.2 ROWID

WITH ROWID

8.3 复杂

-- 多表 JOIN
CREATE MATERIALIZED VIEW mv_emp_dept
  REFRESH FAST ON DEMAND
  AS
  SELECT e.id, e.name, d.dept_name, e.dept_id
  FROM employees e, departments d
  WHERE e.dept_id = d.id;

9. 刷新组

-- 多个 MV 一起刷新
EXEC DBMS_REFRESH.MAKE(
  name => 'refresh_group',
  list => 'mv_emp_dept, mv_dept_avg',
  next_date => SYSDATE,
  interval => 'SYSDATE + 1'
);

10. 监控

10.1 MV 信息

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

10.2 日志

SELECT master, log_table 
FROM user_mview_logs;

10.3 可刷新

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('mv_emp_dept');
SELECT * FROM mv_capabilities_table;

11. 管理

11.1 重建

ALTER MATERIALIZED VIEW mv_emp_dept REBUILD;

11.2 失效

ALTER MATERIALIZED VIEW mv_emp_dept COMPILE;

11.3 删除

DROP MATERIALIZED VIEW mv_emp_dept;
DROP MATERIALIZED VIEW LOG ON employees;

12. 空间

12.1 大小

SELECT 
  segment_name, 
  bytes / 1024 / 1024 AS mb
FROM user_segments
WHERE segment_name LIKE 'MV_%';

12.2 索引

-- 默认主键索引
-- 可建额外索引
CREATE INDEX idx_mv_emp_dept ON mv_emp_dept(dept_id);

13. 常见坑与排错

13.1 FAST 不可用

-- 1. 检查日志
-- 2. 检查 EXPLAIN_MVIEW
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('mv_emp_dept');

13.2 查询不重写

-- 1. 参数
ALTER SESSION SET query_rewrite_enabled = TRUE;

-- 2. 完整性
ALTER SESSION SET query_rewrite_integrity = enforced;

-- 3. 统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

13.3 刷新慢

-- 1. COMPLETE 慢
-- 2. 改 FAST
-- 3. 并行
ALTER MATERIALIZED VIEW mv_emp_dept PARALLEL 4;

14. 最佳实践

视图简化查询：业务
视图 WITH CHECK：完整
物化视图聚合：性能
FAST 刷新：增量
日志维护：必要
查询重写：透明
刷新组：一致
监控空间：管理
EXPLAIN_MVIEW：诊断
文档化：设计

15. 参考资料

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