Oracle视图与物化视图

视图是Oracle数据库中的一种重要数据库对象，用于简化复杂查询、增强安全性和提高数据访问效率。物化视图则是视图的一种特殊形式，它将查询结果存储为物理表，提供更高的查询性能。本文将详细介绍Oracle视图和物化视图的创建、管理、使用和最佳实践。

视图概述

什么是视图？

视图是基于一个或多个表的查询结果的虚拟表，它不存储实际数据，只存储查询定义。视图可以简化复杂查询，隐藏底层表结构，增强数据安全性，并提供一致的数据访问方式。

视图的优势

• 简化复杂查询：将复杂的查询逻辑封装为视图，简化应用程序代码
• 增强安全性：只允许用户访问视图中的特定列，隐藏敏感数据
• 数据一致性：所有应用程序使用相同的视图，确保数据访问的一致性
• 隐藏底层表结构：当底层表结构发生变化时，只需修改视图定义，无需修改应用程序
• 支持复杂计算：可以在视图中包含计算列、聚合函数等复杂逻辑

视图的应用场景

• 简化复杂查询，提高开发效率
• 实现数据安全访问，限制用户只能访问特定数据
• 提供统一的数据访问接口，隔离底层表结构变化
• 实现数据聚合和分析

视图类型

1. 简单视图

基于单个表的视图，不包含聚合函数、DISTINCT、GROUP BY等复杂操作。简单视图可以执行DML操作（INSERT、UPDATE、DELETE），这些操作会直接影响底层表。

-- 创建简单视图
CREATE OR REPLACE VIEW emp_simple_view AS
SELECT employee_id, first_name, last_name, email, hire_date, department_id, salary
FROM employees
WHERE department_id = 60;

2. 复杂视图

基于多个表的视图，或包含聚合函数、DISTINCT、GROUP BY等复杂操作。复杂视图通常不能直接执行DML操作，除非满足特定条件。

-- 创建复杂视图
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary,
       d.department_name, l.city, l.state_province
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
ORDER BY e.department_id, e.salary DESC;

3. 只读视图

通过WITH READ ONLY选项创建的视图，只允许查询操作，不允许DML操作。

-- 创建只读视图
CREATE OR REPLACE VIEW emp_readonly_view AS
SELECT employee_id, first_name, last_name, email, hire_date, department_id, salary
FROM employees
WITH READ ONLY;

4. 检查约束视图

通过WITH CHECK OPTION选项创建的视图，确保DML操作只影响视图中可见的数据。

-- 创建带检查约束的视图
CREATE OR REPLACE VIEW emp_salary_view AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE salary > 5000
WITH CHECK OPTION;

5. 内联视图

在FROM子句中使用的子查询，临时生成的视图，只在当前查询中有效。

-- 使用内联视图
SELECT e.employee_id, e.first_name, e.last_name, e.salary, avg_salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) avg_dept ON e.department_id = avg_dept.department_id
WHERE e.salary > avg_dept.avg_salary;

6. 递归视图

基于自身引用的视图，用于处理层次化数据，如组织结构、文件系统等。

-- 创建递归视图
CREATE OR REPLACE VIEW org_hierarchy_view (level_num, employee_id, first_name, last_name, manager_id, emp_path)
AS
SELECT 1, employee_id, first_name, last_name, manager_id,
       first_name || ' ' || last_name AS emp_path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT oh.level_num + 1, e.employee_id, e.first_name, e.last_name, e.manager_id,
       oh.emp_path || ' > ' || e.first_name || ' ' || e.last_name
FROM employees e
JOIN org_hierarchy_view oh ON e.manager_id = oh.employee_id;

视图的创建和管理

创建视图

-- 创建基本视图
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1, column2, ...]
[WITH READ ONLY | CHECK OPTION];

查看视图定义

-- 查看视图定义
SELECT text FROM user_views WHERE view_name = 'EMP_DEPT_VIEW';

-- 使用DESCRIBE命令查看视图结构
DESCRIBE emp_dept_view;

修改视图

-- 修改视图定义
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary,
       d.department_name, l.city, l.state_province, l.country_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
ORDER BY e.department_id, e.salary DESC;

删除视图

-- 删除视图
DROP VIEW emp_simple_view;

重命名视图

-- 重命名视图
ALTER VIEW emp_old_view RENAME TO emp_new_view;

查看视图依赖

-- 查看视图依赖的对象
SELECT referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'EMP_DEPT_VIEW' AND referenced_type IN ('TABLE', 'VIEW');

视图的DML操作

简单视图的DML操作

简单视图可以直接执行DML操作，这些操作会影响底层表。

-- 向简单视图插入数据
INSERT INTO emp_simple_view (employee_id, first_name, last_name, email, hire_date, department_id, salary)
VALUES (207, 'John', 'Doe', 'johndoe@example.com', SYSDATE, 60, 6000);

-- 更新简单视图数据
UPDATE emp_simple_view
SET salary = salary * 1.1
WHERE employee_id = 103;

-- 删除简单视图数据
DELETE FROM emp_simple_view
WHERE employee_id = 207;

复杂视图的DML操作

复杂视图通常不能直接执行DML操作，除非满足以下条件：

• 视图基于单个表
• 视图包含主键列
• 视图不包含聚合函数、DISTINCT、GROUP BY等
• 视图不包含计算列（除非使用INSTEAD OF触发器）

INSTEAD OF触发器

对于不能直接执行DML操作的视图，可以创建INSTEAD OF触发器来实现DML操作。

-- 创建INSTEAD OF触发器
CREATE OR REPLACE TRIGGER trg_emp_dept_view_insert
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
DECLARE
  v_dept_id departments.department_id%type;
BEGIN
  -- 检查部门是否存在
  SELECT department_id INTO v_dept_id
  FROM departments
  WHERE department_name = :NEW.department_name;
  
  -- 如果部门不存在，抛出异常
  IF v_dept_id IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Department does not exist: ' || :NEW.department_name);
  END IF;
  
  -- 向employees表插入数据
  INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id, salary)
  VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.email, SYSDATE, v_dept_id, :NEW.salary);
END trg_emp_dept_view_insert;
/

物化视图概述

什么是物化视图？

物化视图是一种特殊的视图，它将查询结果存储为物理表，而不是虚拟表。物化视图定期刷新，将底层表的变化反映到物化视图中。物化视图提供了比普通视图更高的查询性能，因为它直接从物理表中获取数据，而不需要重新执行查询。

物化视图的优势

• 提高查询性能：物化视图将查询结果存储为物理表，避免了每次查询都重新执行复杂的查询逻辑
• 支持复杂查询：可以包含复杂的连接、聚合函数等操作
• 减少网络开销：在分布式环境中，物化视图可以将远程数据复制到本地，减少网络传输开销
• 支持数据同步：可以定期或实时刷新，确保数据的一致性

物化视图的应用场景

• 数据仓库和数据集市，用于存储聚合数据
• 分布式环境，用于复制远程数据到本地
• 复杂查询的性能优化
• 数据缓存，减少数据库负载

物化视图类型

1. 基于聚合的物化视图

包含聚合函数（如SUM、AVG、COUNT等）的物化视图，用于数据聚合和分析。

-- 创建基于聚合的物化视图
CREATE MATERIALIZED VIEW emp_dept_salary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT department_id, COUNT(*) AS emp_count, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

2. 基于连接的物化视图

基于多个表连接的物化视图，不包含聚合函数。

-- 创建基于连接的物化视图
CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary,
       d.department_name, d.manager_id, l.city, l.country_id
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

3. 嵌套物化视图

基于其他物化视图的物化视图，可以实现更复杂的数据聚合和分析。

-- 创建嵌套物化视图
CREATE MATERIALIZED VIEW emp_country_salary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT l.country_id, COUNT(*) AS emp_count, SUM(e.salary) AS total_salary
FROM emp_dept_mv e
JOIN locations l ON e.city = l.city
GROUP BY l.country_id;

物化视图的刷新

刷新类型

刷新类型	描述
FAST	增量刷新，只刷新自上次刷新以来的变化数据
COMPLETE	完全刷新，重新执行物化视图查询，重建物化视图
FORCE	尝试快速刷新，如果无法快速刷新则执行完全刷新
NEVER	不自动刷新，需要手动刷新

刷新时机

刷新时机	描述
ON COMMIT	当底层表发生COMMIT操作时自动刷新
ON DEMAND	手动刷新或通过调度作业定期刷新
START WITH	指定首次刷新时间
NEXT	指定下一次刷新时间，基于间隔表达式

物化视图刷新示例

-- 创建定期刷新的物化视图
CREATE MATERIALIZED VIEW emp_salary_mv
BUILD DEFERRED
REFRESH FORCE ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 1/24  -- 每小时刷新一次
ENABLE QUERY REWRITE
AS
SELECT department_id, job_id, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_id;

-- 手动刷新物化视图
EXEC DBMS_MVIEW.REFRESH('emp_salary_mv', 'COMPLETE');
EXEC DBMS_MVIEW.REFRESH('emp_salary_mv', 'FAST');

-- 刷新多个物化视图
EXEC DBMS_MVIEW.REFRESH('emp_salary_mv,emp_dept_mv', 'FORCE');

物化视图的创建和管理

创建物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW view_name
[BUILD {IMMEDIATE | DEFERRED}]
REFRESH [FAST | COMPLETE | FORCE] [ON {COMMIT | DEMAND} | START WITH date NEXT date]
[ENABLE | DISABLE] QUERY REWRITE
AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...];

查看物化视图定义

-- 查看物化视图定义
SELECT query FROM user_mviews WHERE mview_name = 'EMP_DEPT_SALARY_MV';

-- 查看物化视图刷新信息
SELECT mview_name, last_refresh_type, last_refresh_date, next_refresh_date
FROM user_mviews
WHERE mview_name = 'EMP_SALARY_MV';

修改物化视图

-- 修改物化视图
ALTER MATERIALIZED VIEW emp_salary_mv
REFRESH COMPLETE ON DEMAND
NEXT SYSDATE + 2/24;  -- 每2小时刷新一次

-- 启用/禁用查询重写
ALTER MATERIALIZED VIEW emp_salary_mv
ENABLE QUERY REWRITE;

ALTER MATERIALIZED VIEW emp_salary_mv
DISABLE QUERY REWRITE;

删除物化视图

-- 删除物化视图
DROP MATERIALIZED VIEW emp_salary_mv;

物化视图日志

为了支持快速刷新，需要为底层表创建物化视图日志。

-- 为employees表创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON employees
TABLESPACE users
WITH PRIMARY KEY, ROWID, SEQUENCE (department_id, salary, job_id)
INCLUDING NEW VALUES;

-- 为departments表创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON departments
TABLESPACE users
WITH PRIMARY KEY, ROWID;

查询重写

什么是查询重写？

查询重写是Oracle数据库的一项功能，当用户执行查询时，Oracle会自动检查是否存在可以满足查询的物化视图，如果存在，则使用物化视图代替直接查询底层表，从而提高查询性能。

查询重写的优势

• 提高查询性能，避免重复执行复杂查询
• 透明性，用户无需修改查询语句
• 支持复杂查询，包括聚合、连接等

查询重写的条件

• 物化视图必须启用查询重写（ENABLE QUERY REWRITE）
• 物化视图的定义必须满足查询重写的条件
• 数据库参数QUERY_REWRITE_ENABLED必须设置为TRUE
• 数据库参数QUERY_REWRITE_INTEGRITY必须设置为适当的值

查询重写示例

-- 创建物化视图
CREATE MATERIALIZED VIEW emp_dept_salary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT department_id, SUM(salary) AS total_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- 查询重写示例：当执行以下查询时，Oracle会自动使用物化视图
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

视图和物化视图的区别

特性	视图	物化视图
存储方式	虚拟表，不存储数据	物理表，存储查询结果
查询性能	每次查询都重新执行，性能较低	直接从物理表获取数据，性能较高
刷新机制	无刷新机制，始终反映最新数据	需要定期刷新，可能存在数据延迟
存储空间	不需要存储空间	需要存储空间存储数据
适用场景	简化查询，数据安全，一致性访问	复杂查询优化，数据聚合，分布式环境
DML操作	简单视图支持，复杂视图不支持	不直接支持，需要通过刷新机制更新

视图和物化视图的最佳实践

视图最佳实践

1. 使用有意义的命名：视图名称应反映其用途，如emp_dept_view
2. 限制视图列数量：只包含应用程序需要的列，减少网络传输开销
3. 使用WITH READ ONLY：对于不需要DML操作的视图，使用WITH READ ONLY选项
4. 使用WITH CHECK OPTION：对于需要DML操作的视图，使用WITH CHECK OPTION确保数据完整性
5. 避免在视图中使用ORDER BY：除非确实需要，否则不要在视图中使用ORDER BY，因为它会增加额外的开销
6. 定期审查视图：定期审查视图的使用情况，删除不再使用的视图
7. 考虑视图的性能：复杂视图可能会影响查询性能，需要谨慎设计

物化视图最佳实践

1. 选择合适的刷新类型：根据业务需求选择FAST、COMPLETE或FORCE刷新
2. 选择合适的刷新时机：根据数据变化频率和查询性能要求选择刷新时机
3. 创建物化视图日志：为支持快速刷新，为底层表创建物化视图日志
4. 启用查询重写：对于需要提高查询性能的场景，启用查询重写
5. 考虑存储空间：物化视图需要存储空间，应根据数据量大小规划存储空间
6. 定期监控物化视图：监控物化视图的刷新情况和查询性能
7. 使用分区物化视图：对于大型物化视图，考虑使用分区技术提高性能

版本差异

Oracle 12c

• 支持物化视图的自动刷新
• 增强了查询重写功能
• 支持物化视图的并行刷新

Oracle 18c

• 支持物化视图的增量刷新优化
• 增强了物化视图的安全性
• 支持物化视图的云原生部署

Oracle 19c

• 长期支持版本
• 增强了物化视图的性能
• 支持物化视图的自动创建

Oracle 21c

• 支持物化视图的实时刷新
• 增强了物化视图的查询重写功能
• 支持物化视图的JSON数据类型

常见问题（FAQ）

Q: 视图和表有什么区别？

A: 视图是虚拟表，不存储实际数据，只存储查询定义；表是物理表，存储实际数据。视图的查询会转换为对底层表的查询，而表的查询直接访问物理数据。

Q: 物化视图和表有什么区别？

A: 物化视图是基于查询结果的物理表，它会定期刷新以反映底层表的变化；表是独立的物理表，数据直接插入或更新。

Q: 为什么复杂视图不能执行DML操作？

A: 因为复杂视图可能包含聚合函数、DISTINCT、GROUP BY等操作，这些操作会导致视图与底层表之间的关系不明确，无法直接执行DML操作。

Q: 如何确定物化视图是否可以快速刷新？

A: 可以使用DBMS_MVIEW.EXPLAIN_MVIEW函数分析物化视图是否可以快速刷新。

-- 分析物化视图是否可以快速刷新
SET SERVEROUTPUT ON;
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('emp_dept_salary_mv');

SELECT capability_name, possible, related_text
FROM mv_capabilities_table
WHERE name = 'EMP_DEPT_SALARY_MV';

Q: 物化视图的刷新会影响数据库性能吗？

A: 物化视图的刷新会消耗数据库资源，特别是完全刷新和大量数据变化时。应根据业务需求选择合适的刷新类型和时机，避免在业务高峰期刷新。

Q: 如何优化物化视图的查询性能？

A: 可以通过以下方式优化：

• 启用查询重写
• 选择合适的刷新类型和时机
• 为物化视图创建索引
• 考虑使用分区物化视图
• 定期收集物化视图的统计信息

总结

视图和物化视图是Oracle数据库中的重要对象，它们可以简化查询、增强安全性、提高性能和实现数据一致性。视图是虚拟表，不存储实际数据，适合简化查询和实现数据安全访问；物化视图是物理表，存储查询结果，适合复杂查询优化和数据聚合分析。

在实际应用中，应根据业务需求选择合适的视图类型，遵循最佳实践，合理设计和管理视图和物化视图，以提高数据库性能和开发效率。

随着Oracle版本的更新，视图和物化视图的功能不断增强，如自动刷新、实时刷新、云原生部署等，开发人员应关注新版本的特性，充分利用这些功能提高数据库性能和开发效率。