印刻数据库

外观

Oracle 物化视图 ​

物化视图（Materialized View）是 Oracle 数据库中用于提高查询性能的重要技术。它将查询结果预计算并存储在磁盘上，可以显著提高复杂查询的性能，是处理数据仓库和报表系统的有效手段。

物化视图概述 ​

物化视图的定义 ​

物化视图是包含查询结果的数据库对象，它将查询结果预计算并存储在磁盘上，类似于表，但可以定期或实时刷新以反映基础表的变化。

物化视图与普通视图的区别 ​

特性物化视图普通视图
存储存储查询结果到磁盘不存储数据，只存储查询定义
查询性能高，直接访问预计算结果低，每次查询都需要重新执行
刷新机制支持定期或实时刷新不需要刷新，每次查询都动态生成结果
占用空间占用磁盘空间不占用额外磁盘空间
适用场景复杂查询、报表系统、数据仓库简单查询、数据安全性

物化视图的作用 ​

  • 提高查询性能：预计算复杂查询结果，减少查询执行时间
  • 减少网络流量：对于分布式数据库，可以减少远程查询的网络流量
  • 支持数据复制：可以用于数据复制和同步
  • 简化报表生成：可以预计算报表数据，提高报表生成速度
  • 支持查询重写：可以自动将查询重写为使用物化视图，提高查询性能

物化视图类型 ​

基于聚合的物化视图 ​

基于聚合的物化视图包含聚合函数（如 SUM、AVG、COUNT 等），用于预计算聚合查询结果。

创建方法 ​

sql
-- 创建基于聚合的物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT p.product_id, p.product_name, SUM(s.amount) AS total_sales, COUNT(*) AS order_count
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name;

基于连接的物化视图 ​

基于连接的物化视图包含表连接，用于预计算连接查询结果。

创建方法 ​

sql
-- 创建基于连接的物化视图
CREATE MATERIALIZED VIEW mv_employee_department
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

嵌套物化视图 ​

嵌套物化视图是基于其他物化视图创建的物化视图，用于预计算更复杂的查询结果。

创建方法 ​

sql
-- 创建嵌套物化视图
CREATE MATERIALIZED VIEW mv_sales_nested
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT product_id, product_name, total_sales
FROM mv_sales_summary
WHERE total_sales > 10000;

物化视图刷新机制 ​

刷新类型 ​

完全刷新（Complete Refresh） ​

  • 重新执行物化视图的定义查询
  • 替换物化视图中的所有数据
  • 适合数据量较小或变化频繁的场景
  • 刷新命令：DBMS_MVIEW.REFRESH('mv_name', 'C');

快速刷新（Fast Refresh） ​

  • 只刷新基础表中变化的数据
  • 基于物化视图日志（Materialized View Log）
  • 适合数据量较大且变化不频繁的场景
  • 刷新命令：DBMS_MVIEW.REFRESH('mv_name', 'F');

强制刷新（Force Refresh） ​

  • 尝试快速刷新，如果失败则执行完全刷新
  • 刷新命令：DBMS_MVIEW.REFRESH('mv_name', '?');

刷新模式 ​

按需刷新（On Demand） ​

  • 手动或定时刷新
  • 适合数据变化不频繁的场景
  • 刷新命令：DBMS_MVIEW.REFRESH('mv_name');

提交时刷新（On Commit） ​

  • 当基础表发生提交时自动刷新
  • 适合需要实时数据的场景
  • 需要在物化视图定义中指定 REFRESH FAST ON COMMIT

按时间间隔刷新（On Interval） ​

  • 按照指定的时间间隔自动刷新
  • Oracle 12c 及以上版本支持
  • 刷新命令：CREATE MATERIALIZED VIEW mv_name ... REFRESH FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE + INTERVAL '1' HOUR;

物化视图日志 ​

物化视图日志用于记录基础表的变化，是快速刷新的必要条件。

创建方法 ​

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

-- 为表创建包含所有列的物化视图日志
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, PRIMARY KEY, SEQUENCE (
  sale_id, sale_date, product_id, quantity, amount
) INCLUDING NEW VALUES;

物化视图日志类型 ​

  • ROWID：包含行的 ROWID
  • PRIMARY KEY：包含主键列
  • SEQUENCE：包含序列值，用于确定行的变化顺序
  • INCLUDING NEW VALUES：包含变化后的新值

物化视图查询重写 ​

查询重写的定义 ​

查询重写是 Oracle 优化器将用户查询自动重写为使用物化视图的过程，从而提高查询性能。

查询重写的类型 ​

精确重写（Exact Rewrite） ​

  • 查询与物化视图的定义完全匹配
  • 优化器可以直接使用物化视图的结果

一般重写（General Rewrite） ​

  • 查询与物化视图的定义不完全匹配，但优化器可以通过转换使用物化视图的结果
  • 支持聚合函数、分组、连接等复杂查询

查询重写的启用 ​

sql
-- 启用物化视图查询重写
ALTER MATERIALIZED VIEW mv_sales_summary ENABLE QUERY REWRITE;

-- 禁用物化视图查询重写
ALTER MATERIALIZED VIEW mv_sales_summary DISABLE QUERY REWRITE;

-- 查看查询重写设置
SHOW PARAMETER query_rewrite_enabled;

-- 启用实例级查询重写
ALTER SYSTEM SET query_rewrite_enabled = TRUE SCOPE=SPFILE;

查询重写的验证 ​

sql
-- 使用 EXPLAIN PLAN 验证查询重写
EXPLAIN PLAN FOR
SELECT p.product_id, p.product_name, SUM(s.amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name;

-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 如果执行计划中包含 "MAT_VIEW REWRITE"，则表示查询重写成功

物化视图管理 ​

物化视图的创建与修改 ​

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

-- 修改物化视图
ALTER MATERIALIZED VIEW mv_employee_salary
REFRESH COMPLETE
DISABLE QUERY REWRITE;

-- 重命名物化视图
ALTER MATERIALIZED VIEW mv_old_name RENAME TO mv_new_name;

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

物化视图的刷新 ​

sql
-- 手动刷新单个物化视图
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary');

-- 手动快速刷新
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'F');

-- 手动完全刷新
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C');

-- 手动强制刷新
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', '?');

-- 刷新多个物化视图
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary, mv_employee_department');

-- 刷新所有物化视图
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS;

-- 按时间间隔自动刷新
CREATE MATERIALIZED VIEW mv_sales_hourly
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + INTERVAL '1' HOUR
ENABLE QUERY REWRITE
AS
SELECT TRUNC(sale_date, 'HH24') AS sale_hour, SUM(amount) AS total_sales
FROM sales
GROUP BY TRUNC(sale_date, 'HH24');

物化视图的监控 ​

sql
-- 查看物化视图信息
SELECT * FROM dba_mviews WHERE mview_name = 'MV_SALES_SUMMARY';

-- 查看物化视图日志信息
SELECT * FROM dba_mview_logs WHERE master = 'EMPLOYEES';

-- 查看物化视图的刷新状态
SELECT mview_name, last_refresh_type, last_refresh_date, staleness
FROM user_mviews;

-- 查看物化视图的统计信息
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables WHERE table_name = 'MV_SALES_SUMMARY';

-- 收集物化视图的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'MV_SALES_SUMMARY');

-- 验证物化视图是否可以快速刷新
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('HR.MV_SALES_SUMMARY');
SELECT * FROM MV_CAPABILITIES_TABLE;

物化视图的维护 ​

sql
-- 重建物化视图
ALTER MATERIALIZED VIEW mv_sales_summary REBUILD;

-- 重新编译物化视图
ALTER MATERIALIZED VIEW mv_sales_summary COMPILE;

-- 刷新物化视图并收集统计信息
BEGIN
  DBMS_MVIEW.REFRESH('mv_sales_summary');
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'MV_SALES_SUMMARY');
END;
/

物化视图最佳实践 ​

创建最佳实践 ​

  1. 选择合适的物化视图类型：根据业务需求选择基于聚合或基于连接的物化视图
  2. 合理设计刷新机制：根据数据变化频率选择合适的刷新类型和模式
  3. 创建必要的物化视图日志：对于快速刷新，必须创建物化视图日志
  4. 启用查询重写：如果需要自动重写查询，启用查询重写功能
  5. 合理设计物化视图的索引：为物化视图创建合适的索引，提高查询性能
  6. 考虑存储位置：将物化视图存储在与基础表不同的表空间或磁盘上

刷新最佳实践 ​

  1. 选择合适的刷新类型：数据量小时使用完全刷新，数据量大时使用快速刷新
  2. 选择合适的刷新模式：实时数据使用提交时刷新，非实时数据使用按需或按时间间隔刷新
  3. 避免频繁刷新：减少刷新对系统性能的影响
  4. 批量刷新：同时刷新多个物化视图，减少系统开销
  5. 在低峰期刷新：选择系统负载低的时段进行刷新

查询重写最佳实践 ​

  1. 启用查询重写：在物化视图定义中指定 ENABLE QUERY REWRITE
  2. 收集统计信息：定期收集物化视图和基础表的统计信息，确保优化器生成最佳执行计划
  3. 使用精确的物化视图定义：确保物化视图的定义与查询匹配，提高查询重写成功率
  4. 监控查询重写情况：定期检查查询是否被重写，及时调整物化视图定义

性能优化最佳实践 ​

  1. 为物化视图创建索引：根据查询需求为物化视图创建合适的索引
  2. 分区物化视图：对于大型物化视图，使用分区技术提高查询性能
  3. 压缩物化视图：使用表压缩减少物化视图的存储空间
  4. 使用并行执行：对于大型物化视图的刷新和查询，使用并行执行提高性能
  5. 监控物化视图的使用情况：定期监控物化视图的查询次数和刷新时间，及时调整

版本差异 ​

版本特性
Oracle 11g增强了物化视图的快速刷新功能，支持更多聚合函数
Oracle 12c引入了按时间间隔自动刷新，增强了多租户环境的支持
Oracle 18c增强了物化视图的查询重写功能，支持更多查询类型
Oracle 19c优化了物化视图的刷新性能，增强了并行刷新功能
Oracle 23c增强了 JSON 数据的物化视图支持，优化了查询重写算法

常见问题（FAQ） ​

Q1: 如何判断物化视图是否可以快速刷新？ ​

A1: 使用 DBMS_MVIEW.EXPLAIN_MVIEW 函数：

sql
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('HR.MV_SALES_SUMMARY');
SELECT capability_name, possible, msgtxt FROM MV_CAPABILITIES_TABLE;

Q2: 物化视图刷新失败怎么办？ ​

A2: 查看刷新日志：

sql
-- 查看物化视图刷新日志
SELECT * FROM dba_mview_refresh_logs WHERE mview_name = 'MV_SALES_SUMMARY';

-- 查看最近的刷新错误
SELECT * FROM dba_mview_errors WHERE mview_name = 'MV_SALES_SUMMARY';

Q3: 如何提高物化视图的刷新性能？ ​

A3: 提高刷新性能的方法：

  1. 使用快速刷新代替完全刷新
  2. 创建必要的物化视图日志
  3. 使用并行刷新
  4. 在低峰期刷新
  5. 优化基础表的索引

Q4: 物化视图与索引有什么区别？ ​

A4: 物化视图与索引的区别：

  • 物化视图存储查询结果，索引存储指向数据的指针
  • 物化视图适合复杂查询，索引适合简单查询
  • 物化视图的维护成本高于索引
  • 物化视图可以提高聚合查询和连接查询的性能，索引适合单表查询

Q5: 如何监控物化视图的使用情况？ ​

A5: 使用以下查询：

sql
-- 查看物化视图的查询次数
SELECT object_name, executions FROM v$db_object_cache WHERE object_type = 'MATERIALIZED VIEW';

-- 查看物化视图的刷新时间
SELECT mview_name, last_refresh_date, last_refresh_type FROM user_mviews;

-- 查看物化视图的存储空间
SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name LIKE 'MV_%';

Q6: 如何将物化视图用于数据仓库？ ​

A6: 物化视图在数据仓库中的应用：

  1. 预计算聚合结果，提高报表查询性能
  2. 实现数据的增量加载和更新
  3. 支持数据的分层存储
  4. 实现数据的汇总和分析

相关命令速查 ​

命令用途
CREATE MATERIALIZED VIEW创建物化视图
ALTER MATERIALIZED VIEW修改物化视图
DROP MATERIALIZED VIEW删除物化视图
CREATE MATERIALIZED VIEW LOG创建物化视图日志
DROP MATERIALIZED VIEW LOG删除物化视图日志
DBMS_MVIEW.REFRESH刷新物化视图
DBMS_MVIEW.REFRESH_ALL_MVIEWS刷新所有物化视图
DBMS_MVIEW.EXPLAIN_MVIEW解释物化视图的能力
DBMS_STATS.GATHER_TABLE_STATS收集物化视图统计信息
SELECT * FROM dba_mviews查看物化视图信息
SELECT * FROM dba_mview_logs查看物化视图日志信息
SELECT * FROM user_mviews查看当前用户的物化视图信息

总结 ​

物化视图是 Oracle 数据库中用于提高查询性能的重要技术，它将查询结果预计算并存储在磁盘上，可以定期或实时刷新以反映基础表的变化。DBA 应该根据实际业务需求选择合适的物化视图类型和刷新机制，合理设计物化视图的索引和存储，定期进行监控和维护，以确保物化视图的高效运行。通过遵循最佳实践，DBA 可以充分发挥物化视图的优势，提高数据库的查询性能和响应速度，满足业务的高可用性需求。