Oracle物化视图概述

物化视图是一种特殊的视图普通视图不会对数据进行单独存储而物化视图会定期取决于物化视图定义的刷新策略对相应的SQL查询结果数据进行单独存储由于查询结果是预存下来的物化视图的查询结果效率远高于普通的视图。对于统计汇总聚合查询缓慢需要提升查询效率的场景物化视图是常用手段之一在表数据量过大查询历史数据缓慢且数据不会变动时除了改造为分区表也可将需要的历史时段数据创建为物化视图当访问对应时段的数据时会匹配相对应的物化视图重写查询直接从物化视图中获取数据此场景由于数据不会刷新数据库会将物化视图中的数据标记为“过旧”状态数据库的查询重写参数控制级别不能太高否则无法匹配物化视图此时可降低查询重写控制级别如QUERY_REWRITE_INTEGRITY STALE_TOLERATED不会从大表中获取数据。注意数据查询本身比较缓慢创建物化视图建议在低峰期进行创建物化视图时会对基表获取排它锁并产生额外的I/O资源消耗耗时过长会对业务产生一定风险。场景痛点物化视图的作用推荐指数BI 报表/大屏聚合查询慢CPU 100%预聚合存结果查得快⭐⭐⭐⭐⭐多表关联查询Join 太多IO 消耗大预连接存宽表免 Join⭐⭐⭐⭐⭐数据仓库ETL 脚本复杂维护难自动化替代调度脚本⭐⭐⭐⭐异地容灾跨网查询慢带宽贵数据分发本地存副本⭐⭐⭐高频交易系统实时性要求极高慎用刷新延迟可能是瓶颈⭐⭐什么时候不要用数据实时性要求极高物化视图特别是异步刷新有延迟。如果你需要看到毫秒级的最新数据请直接查基表。基表频繁更新如果基表每秒都在发生大量 UPDATE/DELETE维护物化视图日志和刷新的开销可能会超过查询带来的收益甚至拖垮数据库正如你之前遇到的创建日志耗时问题。查询模式不固定如果你的查询全是 Ad-Hoc即席查询没有重复的模式物化视图无法命中只会浪费存储空间。本文以Oracle19c为例进行简单介绍。核心语法公式你可以把创建语句看作是由以下几个模块组成的CREATE MATERIALIZED VIEW 视图名称 [BUILD IMMEDIATE | BUILD DEFERRED] -- 1. 何时生成数据 [REFRESH [FAST | COMPLETE | FORCE]] -- 2. 如何刷新数据 [ON [COMMIT | DEMAND]] -- 3. 何时触发刷新 [START WITH 时间 NEXT 时间间隔] -- 4. 定时刷新设置可选 [ENABLE QUERY REWRITE] -- 5. 是否允许查询重写可选,指定此项查询会匹配该物化视图进行查询重写 AS SELECT ...; -- 6. 定义数据的查询语句参数模块选项含义与区别BUILDIMMEDIATE (默认)创建时立即把数据查出来存好。DEFERRED创建时不存数据空表等第一次刷新时才填充。REFRESHFORCE (默认)智能模式优先尝试增量刷新(FAST)不行则全量刷新(COMPLETE)。FAST增量刷新。只同步变化的数据速度极快但必须创建物化视图日志。COMPLETE全量刷新。每次把整个查询重跑一遍覆盖旧数据。ONDEMAND (默认)手动刷新。需要DBA或定时任务去触发。COMMIT实时刷新。基表一提交事务MV自动同步会有性能损耗。Syntaxcreate_materialized_view::Description of the illustration create_materialized_view.eps案例以下是三种最常见的场景可以参考。场景一最简单的定时全量刷新适用于报表需求每天晚上 10 点统计各部门的工资总额。不需要实时只要数据准就行。特点不需要创建日志配置简单适合数据量不是特别巨大的汇总表。CREATE MATERIALIZED VIEW mv_dept_salary_stat BUILD IMMEDIATE -- 创建时立即生成数据 REFRESH COMPLETE -- 每次彻底重算 START WITH SYSDATE -- 从现在开始 NEXT TRUNC(SYSDATE 1) 22/24 -- 每天 22:00 刷新 (22/24 代表 22小时) AS SELECT department_id, SUM(salary) as total_salary, COUNT(*) as emp_count FROM employees GROUP BY department_id;场景二高性能增量刷新适用于大数据量同步需求需要频繁刷新比如每 5 分钟或者数据量很大全量刷新太慢。前置条件必须先给基表创建物化视图日志否则无法使用FAST模式。第一步创建日志在基表上操作- 假设基表是 orders -- WITH PRIMARY KEY 表示记录主键变化INCLUDING NEW VALUES 表示记录更新后的新值 CREATE MATERIALIZED VIEW LOG ON orders WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;第二步创建物化视图CREATE MATERIALIZED VIEW mv_orders_fast BUILD IMMEDIATE REFRESH FAST -- 开启增量刷新 ON DEMAND -- 手动或定时触发 START WITH SYSDATE NEXT SYSDATE 5/(24*60) -- 每 5 分钟刷新一次 AS SELECT order_id, customer_id, amount FROM orders WHERE status PAID;场景三实时同步适用于数据分发需求基表一旦插入数据物化视图必须立刻能查到。特点使用ON COMMIT基表提交事务时Oracle 会顺便把 MV 也刷了。这会增加基表事务的响应时间。CREATE MATERIALIZED VIEW mv_emp_realtime BUILD IMMEDIATE REFRESH FAST -- 必须是 FAST ON COMMIT -- 基表提交时自动同步 AS SELECT e.emp_id, e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.dept_id d.dept_id;常用管理命令创建好之后你可能需要手动干预或查看状态以下命令非常有用手动刷新立即执行一次-- C 代表 Complete (全量), F 代表 Fast (增量) CALL DBMS_MVIEW.REFRESH(MV_DEPT_SALARY_STAT, C);查看物化视图状态SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM dba_mviews; SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM user_mviews;删除物化视图DROP MATERIALIZED VIEW mv_dept_salary_stat;避坑指南FAST 刷新失败如果你指定了REFRESH FAST但没有创建MATERIALIZED VIEW LOG或者查询语句太复杂如使用了ROWNUM、非主键连接的复杂 Join刷新会报错或自动降级为 COMPLETE。主键要求建议基表必须有主键。如果基表没有主键创建日志时必须用WITH ROWID但这会限制 MV 的功能例如不支持某些聚合函数的快速刷新。列名明确在AS SELECT语句中尽量不要用SELECT *明确写出列名是更安全的做法。物化视图与基表归属用户不是同一个用户时需显示声明指定基表物化视图用户需显示授权GRANT SELECT ON ... TO ...直接授权角色权限方式可能会报错“表或视图不存在”使用快速增量刷新时物化视图用户也需要具备物化视图日志表的查询权限物化视图日志表需跟基表同属一个用户。判断SQL 查询是否利用了物化视图要判断一段 SQL 查询是否真正利用了物化视图即发生了查询重写 Query Rewrite最准确的方法是查看执行计划。如果物化视图生效执行计划中会出现物化视图的名称或者出现MAT_VIEW REWRITE相关的操作。以下是三种最常用的验证方法按推荐程度排序方法一使用EXPLAIN PLAN最常用这是最直接的方法用于查看优化器“计划”如何执行这条语句。操作步骤执行解释计划命令将你的 SQL 语句放入EXPLAIN PLAN FOR后面执行。EXPLAIN PLAN FOR SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;查看执行计划使用DBMS_XPLAN包显示结果。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);结果分析走了物化视图在Operation列中你会直接看到物化视图的名字例如MV_DEPT_SALARY_STAT而不是原始表名。或者看到类似MAT_VIEW REWRITE的步骤。示例输出片段IdOperationName1TABLE ACCESS FULLMV_DEPT_SALARY_STAT没走物化视图你会看到原始表的名字例如EMPLOYEES以及全表扫描或索引扫描。方法二使用AUTOTRACE实时执行如果你不仅想看计划还想看实际执行时的统计信息如逻辑读可以使用 AUTOTRACE。这需要你的用户有PLUSTRACE角色权限。操作步骤SET AUTOTRACE ON EXPLAIN -- 然后执行你的查询 SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;结果分析查看输出的Execution Plan部分逻辑同上。如果Name列显示的是物化视图的名称说明重写成功。方法三查询V$SQL_PLAN针对已执行的 SQL如果 SQL 已经在生产环境运行过了你可以从共享池中抓取它的执行计划。操作步骤你需要知道该 SQL 的SQL_ID。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(你的SQL_ID, NULL, ALL));结果分析同样观察OBJECT_NAME列是否为物化视图的名称。核心检查为什么没走物化视图当你执行一条 SQL 时Oracle 内部大概发生了以下步骤解析器“用户查SELECT dept, SUM(sal) FROM emp。”重写器“我手里有个物化视图MV_EMP_DEPT好像存了这个数据。让我看看……嗯表对得上列也有聚合函数也对语义匹配成功。”优化器“好核算一下。查基表EMP需要 1000 个 IO查物化视图MV_EMP_DEPT只要 10 个 IO。成本更低COST成本计算成功”检查员“视图状态是FRESH数据可信。完整性检查通过”执行器“把 SQL 偷偷换成SELECT * FROM MV_EMP_DEPT执行”一句话总结监测靠执行计划原理靠语义匹配和成本比较如果你发现执行计划里只有原表没有物化视图通常是因为以下原因按可能性排序未开启查询重写会话级开启ALTER SESSION SET QUERY_REWRITE_ENABLED TRUE;系统级开启ALTER SYSTEM SET QUERY_REWRITE_ENABLED TRUE SCOPEBOTH;创建时开启创建 MV 时使用了ENABLE QUERY REWRITE子句。--查询物化视图状态 SELECT owner, mview_name, staleness, -- 核心状态 compile_state, -- 编译状态 last_refresh_type, -- 上次刷新类型 last_refresh_date -- 上次刷新时间 FROM dba_mviews WHERE mview_name 你的物化视图名称; --查询数据库有没有开启查询重写 SELECT name, value FROM v$parameter WHERE name query_rewrite_enabled; --查询物化视图本身是否允许查询重写 SELECT mview_name, rewrite_enabled FROM user_mviews WHERE mview_name 你的物化视图名称;物化视图数据过时如果 MV 状态是UNUSABLE或数据太旧STALE且参数QUERY_REWRITE_INTEGRITY设置为STALE_TOLERATED以外的值优化器可能不敢用。修改QUERY_REWRITE_INTEGRITY参数--会话级 ALTER SESSION SET QUERY_REWRITE_INTEGRITY STALE_TOLERATED; --系统级针对动态参数立即生效不需重启数据库 ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY STALE_TOLERATED SCOPEBOTH;检查状态ELECT MVIEW_NAME, STALENESS, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME 你的物化视图名;STALENESS为FRESH是最佳状态。COMPILE_STATE必须是VALID。权限不足当前用户必须有访问该物化视图的权限。不满足重写条件你的 SQL 查询的列或过滤条件超出了物化视图定义的范围例如 MV 只聚合了dept_id但你查询了job_id除非 MV 包含job_id或使用了更高级的重写技术否则无法匹配。