Oracle SQL物化视图实现复杂预计算汇总

物化视图不是缓存，是个需要伺候的物理表

很多人一听说“物化视图”就以为它能自动搞定 GROUP BY 查询的预计算，仿佛建完 CREATE MATERIALIZED VIEW mv_sales_sum 就能一劳永逸。然而现实是——查询结果还是旧的，而 INSERT INTO fact_sales 已经跑完好几轮了。问题出在哪儿？Oracle 默认的刷新策略是 ON DEMAND，需要你手动触发，不是实时同步。

• 想提交即更新？得用 REFRESH FAST ON COMMIT，但仅限单表，而且基表必须有 MATERIALIZED VIEW LOG
• 跨多表 JOIN 的物化视图不支持 ON COMMIT，只能靠定时或手动刷新
• 刷新命令是 DBMS_MVIEW.REFRESH('mv_sales_sum')，不是 REFRESH MATERIALIZED VIEW（那是 PostgreSQL 的语法）

一句话总结：物化视图本质是一张物理表，它的数据更新完全取决于你设定的刷新策略，别指望它像内存缓存一样自动生效。

FAST 刷新不是开箱即用，依赖物化视图日志配置

启用 FAST 刷新之前，必须在每个基表上创建日志，否则 REFRESH FAST 会静默退化成 COMPLETE——不报错，但性能直接降级。更坑的是，日志创建语句必须包含所有被 SELECT 的列，比如：

CREATE MATERIALIZED VIEW LOG ON fact_sales WITH ROWID, SEQUENCE(sales_amt, region_id) INCLUDING NEW VALUES

如果物化视图涉及 JOIN，所有参与表都得建日志，且字段列表要对齐。另外，WITH PRIMARY KEY 比 WITH ROWID 更安全，但要求基表有主键；没主键时只能用 ROWID，可一旦表结构变更（比如重建表），日志就失效了。

多表 JOIN 场景下，维度表更新极易导致刷新变慢

举个例子：物化视图定义里写了 LEFT JOIN dim_promotion，而该维度表每天全量覆盖（TRUNCATE + INSERT），哪怕只有一行促销信息变了，整个物化视图都得重算——Oracle 无法判断哪些事实行真正受影响。这时候有几个改进方向：

• 优先用 INNER JOIN，避免 LEFT/RIGHT JOIN 引入不确定性
• 维度表尽量用 SCD Type 1（覆盖更新），别用 Type 2（新增版本行）；如果必须用 Type 2，物化视图 SQL 中得显式加 WHERE valid_from <= SYSDATE AND valid_to > SYSDATE
• 更稳妥的做法是宽表化：把 region_name、product_category 等字段冗余进事实表，然后基于单表建物化视图

查询是否真走物化视图，得看执行计划里的实际扫描源

很多人只看 EXPLAIN PLAN 输出里有没有物化视图的名字，以为出现就万事大吉。但实际情况是，Oracle 的查询重写（QUERY REWRITE）可能被禁用，或因统计信息过期、一致性级别太严而跳过。排查步骤很清晰：

• 启用重写：建视图时加 ENABLE QUERY REWRITE，并确保参数 QUERY_REWRITE_ENABLED = TRUE
• 验证是否命中：运行 EXPLAIN PLAN FOR SELECT sum(sales_amt) FROM fact_sales JOIN dim_region USING(region_id);，再查 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)，看 Plan 列是否出现 TABLE ACCESS FULL on mv_sales_sum
• 如果没命中，检查 QUERY_REWRITE_INTEGRITY 设置：设为 STALE_TOLERATED 可容忍少量过期数据，提高重写成功率

最容易被忽略的一点：物化视图本身是一张物理表，它的统计信息也要定期收集（DBMS_STATS.GATHER_TABLE_STATS），否则优化器可能误判成本，宁愿扫大基表也不走物化视图。这一点和普通表没什么区别。