你是否也在数据量一大，查询就“跑断气”？
本文手把手教你用 Oracle 物化视图 + 查询重写提速 25 倍！

一、物化视图与查询重写的核心概念

1.1 物化视图的本质

物化视图（Materialized View）是一种特殊的数据库对象，它将查询结果集持久化存储在表中，不同于普通视图的实时计算特性。在数据仓库、OLAP 分析等场景中，物化视图通过预先计算聚合结果、连接数据等操作，将高频查询的结果集缓存，从而大幅提升查询性能。

1.2 查询重写的技术价值

查询重写（Query Rewrite）是 Oracle 优化器的核心能力之一，当用户查询与物化视图的定义兼容时，优化器会自动将查询重定向到物化视图，直接读取预计算结果。这种机制对应用层完全透明，无需修改 SQL 即可实现性能优化。例如：

• 原查询：扫描事实表并计算聚合（需处理百万行数据）

• 重写后查询：直接读取物化视图（仅需处理数十行聚合结果）

1.3 优化器的决策逻辑：基于成本的智能选择

Oracle 优化器并非无条件使用物化视图重写查询，而是通过成本模型比较两种执行路径：

• 使用物化视图的计划成本：读取物化视图数据的 IO 与计算开销

• 不使用物化视图的计划成本：直接访问基表的开销。仅当前者成本更低时，优化器才会触发查询重写。

二、查询重写的技术原理与实现机制

2.1 语义级匹配

当文本不匹配时，优化器会分析查询与物化视图的语义兼容性，包括：

• 连接条件（如sales.time_id = times.time_id）

• 选择条件（如time_id > '2023-01-01'）

• 聚合函数（如SUM(amount_sold)）

• 分组列（如GROUP BY calendar_month）

2.2 查询重写的适用范围

支持以下 SQL 场景：

• DML 语句：SELECT、CREATE TABLE AS SELECT、INSERT INTO SELECT

• 集合运算符：UNION、INTERSECT、MINUS等

• DML 子查询：INSERT、DELETE、UPDATE中的子查询部分

三、关键初始化参数

查询重写行为由某些数据库初始化参数控制。

表1-1 控制查询重写行为的初始化参数

四、关于查询重写的准确性

查询重写提供了三个级别的重写完整性，由初始化参数 QUERY_REWRITE_INTEGRITY 控制。

您可以为 QUERY_REWRITE_INTEGRITY 参数设置的值如下

• ENFORCED

这是默认模式。优化器仅使用来自物化视图的最新数据，并且仅使用基于已启用且已验证的主键、唯一键或外键约束的那些关系。

• TRUSTED

在可信模式下，优化器信任在维度和 RELY 约束中声明的关系是正确的。在此模式下，优化器还使用预构建的物化视图或基于视图的物化视图，并且它既使用强制关系，也使用非强制关系。它还信任已声明但未启用且未验证的主键或唯一键约束以及使用维度指定的数据关系。此模式提供了更强的查询重写能力，但如果您声明的任何可信关系不正确，也会产生结果错误的风险。

• STALE_TOLERATED

在陈旧数据容忍模式下，优化器使用有效的但包含陈旧数据的物化视图以及包含最新数据的物化视图。此模式提供了最大的重写能力，但会产生生成不准确结果的风险。 

如果重写完整性设置为最安全级别FORCE，优化器仅使用强制主键约束和参照完整性约束，以确保查询结果与直接访问明细表时的结果相同。

如果重写完整性设置为FORCE以外的级别，在几种情况下，使用重写的输出可能与不使用重写的输出不同：

物化视图可能与数据的主副本不同步。这通常是因为在对物化视图的一个或多个明细表进行批量加载或DML操作后，物化视图刷新过程处于挂起状态。在一些数据仓库站点，这种情况是可取的，因为某些物化视图按特定时间间隔进行刷新并不罕见。

• 维度对象所隐含的关系无效。例如，层次结构中某个级别的值不能精确汇总到单个父值。

• 预建物化视图表中存储的值可能不正确。

• 由于未实施的表或视图约束所定义的数据关系错误，可能会导致错误的答案。

你可以在初始化参数文件中设置QUERY_REWRITE_INTEGRITY，也可以使用ALTER SYSTEM或ALTER SESSION语句进行设置。

五、查询重写的典型案例与性能对比

5.1 案例分析

场景：某零售企业需高频查询各月销售总额，事实表sales含 1000 万行数据，times表含 365 行时间维度数据。

5.2 实现步骤

1. 原始查询（未重写）：

SELECT t.calendar_month_desc, SUM(s.amount_sold)  
FROM sales s JOIN times t ON s.time_id = t.time_id  
GROUP BY t.calendar_month_desc;  

Execution Plan
----------------------------------------------------------
Plan hash value: 2607197432

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    60 |  2220 |  3589   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY          |          |    60 |  2220 |  3589   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN             |          |  1459 | 53983 |  3588   (1)| 00:00:01 |       |       |
|   3 |    VIEW                 | VW_GBC_5 |  1459 | 30639 |  3571   (1)| 00:00:01 |       |       |
|   4 |     HASH GROUP BY       |          |  1459 | 18967 |  3571   (1)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE ALL|          |   918K|    11M|  3551   (1)| 00:00:01 |     1 |    15 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|    11M|  3551   (1)| 00:00:01 |     1 |    15 |
|   7 |    TABLE ACCESS FULL    | TIMES    |  1826 | 29216 |    17   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="T"."TIME_ID")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
       2745  recursive calls
          0  db block gets
       9136  consistent gets
       4908  physical reads
          0  redo size
       2181  bytes sent via SQL*Net to client
        183  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
        234  sorts (memory)
          0  sorts (disk)
         48  rows processed

执行成本：需扫描 1000 万行数据，完成连接与聚合，响应时间约 5 秒。

2.  创建物化视图：

CREATE MATERIALIZED VIEW cal_month_sales_mv  
ENABLE QUERY REWRITE AS  
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars  
FROM sales s JOIN times t ON s.time_id = t.time_id  
GROUP BY t.calendar_month_desc;  

3. 重写后查询：

SELECT t.calendar_month_desc, SUM(s.amount_sold)  
FROM sales s JOIN times t ON s.time_id = t.time_id  
GROUP BY t.calendar_month_desc;  
-- 优化器自动重写为  
SELECT calendar_month_desc, dollars FROM cal_month_sales_mv;  
--执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2368247697

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |    48 |   720 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| CAL_MONTH_SALES_MV |    48 |   720 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        976  recursive calls
          0  db block gets
       1505  consistent gets
          0  physical reads
          0  redo size
       2181  bytes sent via SQL*Net to client
        183  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         82  sorts (memory)
          0  sorts (disk)
         48  rows processed

执行成本：仅扫描物化视图的 48行数据，响应时间降至 200ms 以内。

五、可靠性验证

5.1 重写可行性验证

使用DBMS_MVIEW.EXPLAIN_REWRITE过程诊断查询是否可重写：

DECLARE
  qrytext VARCHAR2(500)  :='SELECT t.calendar_month_desc, SUM(s.amount_sold) FROM sales s JOIN times t ON s.time_id = t.time_id GROUP BY t.calendar_month_desc';
    idno    VARCHAR2(30) :='cal_month_sales_mv';
BEGIN
  DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno);
END;
/

查看输出

SELECT message FROM rewrite_table ORDER BY sequence;
---MESSAGE
QSM-01151: query was rewritten
QSM-01360: query rewritten using ANSI join pass
QSM-01209: query rewritten with materialized view, CAL_MONTH_SALES_MV, using text match algorithm

从上面的输出可以确认，分析的查询可以重写。

5.2 数据一致性风险与应对

风险场景：

• 物化视图刷新滞后，数据陈旧（STALE_TOLERATED模式）

• 维度关系声明错误（TRUSTED模式）

应对策略：

• 定期刷新物化视图（如REFRESH COMPLETE ON DEMAND）

• 使用ENABLE VALIDATE确保约束有效性

• 通过V$MVIEW_ERRORS监控物化视图异常

总结：什么场景一定要考虑物化视图重写？

• 查询写法固定、重复度高

• 聚合复杂或多维分析场景（如 BI 报表）

• 数据量大（100W+ 起步）

• 能接受一定的延迟数据（可异步刷新）

🎯只要你用对方法，查询速度飞跃不是梦！

📢 彩蛋：这篇文章适合谁收藏？

✅ DBA
✅ 数据开发工程师
✅ 性能优化工程师
✅ 需要 BI 查询加速的开发者
✅ 想了解 Oracle 高级特性的人

❤️ 最后，别忘了三连！

👉 点赞 + 收藏 + 关注
📬 更多 SQL 优化干货持续更新中
💬 欢迎评论区留言，聊聊你踩过的性能坑！

---

🚀 更多数据库干货，欢迎关注【安呀智数据坊】

如果你觉得这篇文章对你有帮助，欢迎点赞 👍、收藏 ⭐ 和留言 💬 交流，让我知道你还想了解哪些数据库知识！

📬 想系统学习更多数据库实战案例与技术指南？

📊 实战项目分享

📚 技术原理讲解

🧠 数据库架构思维

🛠 工具推荐与实用技巧

立即关注，持续更新中 👇