一、开篇场景：报表SQL从30分钟到1秒的魔法
某数据仓库的日报SQL需要关联5张大表，进行复杂的GROUP BY和SUM聚合，执行时间长达30分钟。业务部门每天需要等半小时才能看到报表数据。
DBA创建了一个物化视图：
CREATEMATERIALIZEDVIEWmv_daily_sales BUILD IMMEDIATE REFRESH FASTONDEMANDENABLEQUERY REWRITEASSELECTproduct_id,region_id,TRUNC(sale_date)ASsale_day,SUM(amount)AStotal_amount,COUNT(*)ASorder_countFROMsalesGROUPBYproduct_id,region_id,TRUNC(sale_date);神奇的事情发生了——Oracle自动将原来的复杂SQL重写为查询物化视图，执行时间从30分钟降到1秒。
这就是物化视图的魔力。它是预先计算并存储的查询结果集，就像一张“缓存表”，可以自动响应相关查询而无需重新执行复杂SQL。今天这篇文章，带你深入理解物化视图的查询重写机制、刷新策略和性能优化。
二、全景架构图：物化视图的工作机制
先通过一张架构图，看清物化视图从创建到查询重写的完整流程：
关键模块解读：
- 橘色（容器表）：物化视图物理存储的表，实际保存数据。
- 绿色（FAST刷新）：增量刷新，只更新变化的数据，效率最高。
- 蓝色（查询重写）：CBO自动将原始SQL重写为查询物化视图。
- 红色（重写决策）：CBO评估重写是否可行、是否代价更低。
三、物化视图的核心概念
1.物化视图的本质定义
精确描述：
物化视图（Materialized View）是预先计算并物理存储的查询结果集。它不同于普通视图（只存储SQL定义），物化视图实际存储数据，因此查询时不需要重新执行基表的SQL。
物化视图与普通视图的区别：
| 对比维度 | 普通视图（View） | 物化视图（Materialized View） |
|---|---|---|
| 存储内容 | 只存储SQL定义 | 存储实际数据 |
| 查询性能 | 每次执行原始SQL | 直接查询预计算结果 |
| 数据新鲜度 | 实时（基表最新） | 取决于刷新策略 |
| 占用空间 | 不占用 | 占用额外存储空间 |
| 索引 | 不能单独建索引 | 可以创建索引 |
| 分区 | 不能分区 | 可以分区 |
2.物化视图日志（Materialized View Log）
精确描述：
物化视图日志是基表上的一个内部表，记录自上次刷新以来基表发生的所有DML变更（INSERT/UPDATE/DELETE）。它是FAST REFRESH（增量刷新）的前提条件。
创建物化视图日志：
-- 创建物化视图日志（必须包含ROWID）CREATEMATERIALIZEDVIEWLOGONsalesWITHROWID,SEQUENCE(product_id,region_id,sale_date,amount)INCLUDING NEWVALUES;-- 查看物化视图日志SELECTmaster,log_table,rowids,sequences,last_refresh_dateFROMdba_mview_logsWHEREmaster='SALES';物化视图日志的内容：
SYS.SNAPLOG$_SALES 表： - SNAPTIME$$：快照时间 - DMLTYPE$$：操作类型（I=INSERT, U=UPDATE, D=DELETE） - OLD_NEW$$：旧值/新值标记 - CHANGE_VECTOR$$：变更向量 - M_ROW$$：被修改行的ROWID四、物化视图的刷新策略
1.刷新模式的三种类型
| 刷新模式 | 命令关键字 | 说明 | 适用场景 |
|---|---|---|---|
| FAST | REFRESH FAST | 增量刷新，只更新变更的数据 | 大表、少量变更 |
| COMPLETE | REFRESH COMPLETE | 完全刷新，重建整个物化视图 | 小表、大量变更 |
| FORCE | REFRESH FORCE | 优先FAST，无法FAST则COMPLETE | 推荐默认选择 |
2.刷新时机的四种选择
ON COMMIT（提交时刷新）：
-- 基表COMMIT时自动刷新CREATEMATERIALIZEDVIEWmv_sales_summary REFRESH FASTONCOMMITASSELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;- 数据实时性最高。
- 每次COMMIT都有额外开销，适合基表变更频率低且对实时性要求高的场景。
ON DEMAND（按需刷新）：
-- 手动或定时刷新CREATEMATERIALIZEDVIEWmv_sales_summary REFRESH FASTONDEMANDASSELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;-- 手动刷新EXECDBMS_MVIEW.REFRESH('MV_SALES_SUMMARY','F');- 灵活性最高。
- 适合数据仓库、报表系统，可以定时批量刷新。
使用DBMS_JOB/DBMS_SCHEDULER定时刷新：
-- 创建定时刷新任务BEGINDBMS_SCHEDULER.CREATE_JOB(job_name=>'refresh_mv_sales',job_type=>'PLSQL_BLOCK',job_action=>'BEGIN DBMS_MVIEW.REFRESH(''MV_SALES_SUMMARY'', ''F''); END;',start_date=>SYSTIMESTAMP,repeat_interval=>'FREQ=HOURLY; INTERVAL=1',enabled=>TRUE);END;/NEVER REFRESH（永不刷新）：
-- 静态快照，创建后不刷新CREATEMATERIALIZEDVIEWmv_static_snapshot NEVER REFRESHASSELECT*FROMsalesWHEREsale_date<DATE'2024-01-01';3.FAST REFRESH的前置条件
不是所有物化视图都支持FAST REFRESH。必须满足以下条件：
-- 检查物化视图是否支持FAST REFRESHEXECDBMS_MVIEW.EXPLAIN_MVIEW('MV_SALES_SUMMARY');-- 查看分析结果SELECTcapability_name,possible,related_text,msgtxtFROMmv_capabilities_tableWHEREcapability_nameLIKE'%FAST%'ORDERBYseq;-- 结果中possible='Y'表示支持FAST REFRESH支持FAST REFRESH的常见聚合函数：
- COUNT, SUM, AVG, MIN, MAX
- COUNT(*), COUNT(column)
- VARIANCE, STDDEV（部分支持）
不支持FAST REFRESH的场景：
- 使用了DISTINCT（某些情况）。
- 使用了CONNECT BY。
- 使用了子查询（某些情况）。
- 使用了ANALYTIC函数。
五、查询重写（Query Rewrite）
1.查询重写的本质
精确描述：
查询重写是CBO优化器的自动机制，当用户提交的SQL与物化视图的定义相匹配时，CBO自动将SQL重写为查询物化视图，而用户完全无感知。
查询重写的三种级别：
| 级别 | 说明 |
|---|---|
| EXACT MATCH | 查询SQL与物化视图定义完全一致 |
| PARTIAL MATCH | 查询SQL可以从物化视图的数据中推导 |
| GENERAL REWRITE | 使用更复杂的推导规则（如聚合上卷） |
2.查询重写的实例演示
场景一：精确匹配
-- 物化视图定义CREATEMATERIALIZEDVIEWmv_sales_summaryENABLEQUERY REWRITEASSELECTproduct_id,SUM(amount)AStotal_amountFROMsalesGROUPBYproduct_id;-- 用户SQL（与物化视图完全一致）SELECTproduct_id,SUM(amount)FROMsalesGROUPBYproduct_id;-- CBO自动重写为：SELECT * FROM mv_sales_summary;场景二：部分匹配（上卷）
-- 用户SQL（查询所有产品的总和）SELECTSUM(amount)FROMsales;-- CBO自动重写为：SELECT SUM(total_amount) FROM mv_sales_summary;场景三：部分匹配（JOIN回基表）
-- 用户SQL（查询产品名称和总金额）SELECTp.product_name,SUM(s.amount)FROMsales sJOINproducts pONs.product_id=p.product_idGROUPBYp.product_name;-- CBO自动重写为：-- SELECT p.product_name, m.total_amount-- FROM mv_sales_summary m JOIN products p ON m.product_id = p.product_id;3.启用查询重写的参数
-- 会话级别启用查询重写ALTERSESSIONSETquery_rewrite_enabled=TRUE;-- 系统级别启用查询重写ALTERSYSTEMSETquery_rewrite_enabled=TRUE;-- 物化视图级别启用查询重写ALTERMATERIALIZEDVIEWmv_sales_summaryENABLEQUERY REWRITE;-- 查看查询重写是否生效EXPLAINPLANFORSELECTSUM(amount)FROMsales;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);-- 执行计划中出现MAT_VIEW REWRITE ACCESS FULL表示重写成功查询重写的限制：
- 会话必须设置
QUERY_REWRITE_INTEGRITY为TRUSTED或STALE_TOLERATED。 - 物化视图的数据必须足够“新鲜”（基于刷新状态判断）。
- 查询SQL不能包含某些Hint（如
NO_REWRITE）。
六、物化视图的索引与分区
1.为物化视图创建索引
-- 物化视图上创建索引CREATEINDEXidx_mv_sales_productONmv_sales_summary(product_id);-- 创建主键（需要物化视图包含主键列）ALTERMATERIALIZEDVIEWmv_sales_summaryADDPRIMARYKEY(product_id);2.物化视图分区
-- 创建分区物化视图CREATEMATERIALIZEDVIEWmv_sales_partitionedPARTITIONBYRANGE(sale_day)(PARTITIONp202401VALUESLESS THAN(DATE'2024-02-01'),PARTITIONp202402VALUESLESS THAN(DATE'2024-03-01'),PARTITIONp202403VALUESLESS THAN(DATE'2024-04-01'))REFRESH FASTONDEMANDENABLEQUERY REWRITEASSELECTproduct_id,region_id,TRUNC(sale_date)ASsale_day,SUM(amount)AStotal_amountFROMsalesGROUPBYproduct_id,region_id,TRUNC(sale_date);七、物化视图的监控与维护
1.查看物化视图状态
-- 查看物化视图的基本信息SELECTmview_name,container_name,refresh_mode,refresh_method,last_refresh_type,last_refresh_date,stale_since,compile_stateFROMdba_mviewsWHEREmview_name='MV_SALES_SUMMARY';-- 查看物化视图大小SELECTsegment_name,SUM(bytes)/1024/1024ASsize_mb,SUM(blocks)ASblocksFROMdba_segmentsWHEREsegment_name='MV_SALES_SUMMARY'GROUPBYsegment_name;-- 查看查询重写统计SELECTname,valueFROMv$sysstatWHEREnameLIKE'%rewrite%';2.物化视图失效处理
-- 查看失效的物化视图SELECTmview_name,compile_state,stalenessFROMdba_mviewsWHEREcompile_state='NEEDS_COMPILE';-- 重新编译物化视图ALTERMATERIALIZEDVIEWmv_sales_summary COMPILE;-- 完全刷新物化视图EXECDBMS_MVIEW.REFRESH('MV_SALES_SUMMARY','C');八、总结：记住这个“预计算”类比就够了
【物化视图是提前做好的菜，而不是现做的菜】
- 普通视图：一道菜的配方（SQL定义）。每次点菜（查询）都要现做（执行SQL）。
- 物化视图：提前做好并放在保温柜里的菜（预计算的结果集）。点菜时直接上菜（查询物化视图），速度快得多。
- FAST REFRESH：只有食材变化时才更新那道菜，而不是重做整桌菜。
- 查询重写：你点的菜虽然和保温柜里的不完全一样，但厨师发现可以用保温柜里的菜稍微加工一下（上卷、JOIN），比从头做快。
- 物化视图日志：食材变更记录本，记录哪些食材被修改了。
物化视图是数据仓库和报表系统的核心优化手段。合理使用物化视图+查询重写，可以将复杂聚合查询的响应时间从分钟级降到秒级。但要注意刷新策略的选择——ON COMMIT保证实时性但有开销，ON DEMAND灵活但数据有延迟。
你在生产环境中使用过哪些类型的物化视图？是否遇到过查询重写不生效的情况？欢迎在评论区分享你的实战经验。
