DB / Oracle / SQL 开发

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

2026.09.06

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