往期回顾：

物化视图（Materialized View）是Oracle提供的强大优化特性，通过预计算和存储查询结果，实现查询重写（Query Rewrite）优化。本文作为《左右SQL执行计划妙招》系列第十四篇，展示物化视图对执行计划的深刻影响。

环境准备：构建测试表

drop materialized view MV_COUNT_T;
drop table t purge;
-- 创建测试表，使用dba_objects数据
create table t as select * from dba_objects;
set autotrace traceonly
set linesize 1000

对比实验：物化视图的性能优势

语句1：未使用物化视图的普通查询

-- 语句1（未建物化视图）
select COUNT(*) FROM T;

执行计划：

------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 80592 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
-------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       1047  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

语句2：启用物化视图后的查询重写

-- 创建物化视图
create materialized view mv_count_t
    build immediate
    refresh on commit
    enable query rewrite
as
    select count(*) FROM T;
-- 语句2（相同SQL，但已建物化视图）
select COUNT(*) FROM T;

执行计划：

-------------------------------------------------------------------------------------------
Plan hash value: 3655891017
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_COUNT_T |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
统计信息
-------------------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

执行计划深度解析

关键差异对比

在这里插入图片描述

查询重写机制详解

语句1的执行路径：

1. 全表扫描：读取80,592行数据

2. 排序聚合：执行COUNT(*)计算

3. 资源消耗：1047次逻辑读

语句2的执行路径：

1. 查询重写：Oracle自动识别可用物化视图

2. 直接访问：从物化视图读取预计算结果

3. 资源优化：仅3次逻辑读，性能提升99.7%

物化视图技术原理

核心优化机制

- 预计算存储：将复杂聚合结果提前计算并持久化

- 查询重写：优化器自动将原始查询重写为物化视图访问

- 透明优化：应用层SQL无需修改，自动获得性能提升

物化视图配置要点

create materialized view mv_count_t
    build immediate        -- 立即构建
    refresh on commit      -- 提交时刷新
    enable query rewrite   -- 启用查询重写
as
    select count(*) FROM T;

应用场景与最佳实践

适用场景

- 复杂聚合查询：SUM、COUNT、AVG等统计操作

- 报表查询频繁：相同查询被反复执行

- 数据变化较少：基础数据更新频率适中

- 跨表关联查询：多表JOIN的复杂查询

实际应用示例

-- 销售报表物化视图
create materialized view mv_sales_summary
    refresh fast on demand
    enable query rewrite
as
    select region, product_type, 
           sum(sales_amount) as total_sales,
           count(*) as order_count
    from sales_detail
    group by region, product_type;

性能监控要点

- 命中率监控：查看物化视图使用频率

- 刷新策略：根据数据更新频率调整刷新机制

- 存储开销：平衡查询性能与存储成本

注意事项与限制

使用考虑

- 数据一致性：需要合理设置刷新策略

- 存储开销：物化视图占用额外存储空间

- 维护成本：DML操作可能影响刷新性能

优化建议

1. 选择合适的刷新策略：根据数据更新频率选择ON COMMIT或ON DEMAND

2. 监控查询重写：确保查询能够正确命中物化视图

3. 定期维护：及时清理不再使用的物化视图

总结

物化视图通过查询重写机制能够显著优化复杂查询的性能。从本案例可以看出，物化视图将逻辑读从1047次减少到3次，执行时间从4秒优化到1秒，实现了近100倍的性能提升。

在OLAP和报表场景中，合理使用物化视图是提升系统性能的重要手段。关键是要根据业务特点选择合适的物化视图策略，在查询性能、数据一致性和维护成本之间找到最佳平衡点。

在这里插入图片描述

未完待续..

左右SQL执行计划妙招 ⑮ ——执行计划利用设计特性改变【列空值影响】

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑭ ——执行计划利用设计特性改变【物化视图影响】