Oracle 19c 物化视图（Materialized View）开发文档

一、概述

1.1 定义

物化视图是包含查询结果集的数据库对象，与普通视图（逻辑上的虚拟表）不同，它物理存储数据，可定期或实时刷新以同步源表数据。Oracle 19c 中，物化视图是数据仓库、报表系统和分布式数据库中提升查询性能的核心工具。

1.2 与普通视图的区别

特性	普通视图（View）	物化视图（Materialized View）
数据存储	不存储数据，查询时动态计算	物理存储数据，查询直接读取
性能	依赖源表索引，复杂查询较慢	预计算结果，查询性能优异
维护成本	无额外成本	需要存储空间，需配置刷新机制
适用场景	简单查询、数据权限控制	复杂聚合查询、报表生成、数据同步

1.3 主要用途

• 加速复杂查询（如多表关联、聚合计算）
• 实现分布式数据库的数据同步（如主从库数据复制）
• 为数据仓库提供预计算的汇总数据
• 减少对源表的直接访问，降低核心业务表压力

二、前提条件

2.1 权限要求

• 创建物化视图：CREATE MATERIALIZED VIEW 权限
• 创建其他用户模式下的物化视图：CREATE ANY MATERIALIZED VIEW 权限
• 刷新物化视图：对源表的 SELECT 权限，若刷新方式为 FAST 需 SELECT 权限 on 物化视图日志
• 启用查询重写：QUERY REWRITE 系统权限或对象级权限

2.2 存储要求

• 需为物化视图分配表空间（默认使用用户的默认表空间）
• 预估数据量并规划存储空间（与源表数据量或聚合结果量相关）

2.3 源表要求

• 若使用快速刷新（FAST REFRESH），源表需创建物化视图日志（Materialized View Log）
• 源表需有稳定的主键或唯一约束（部分刷新方式依赖）

三、物化视图类型

3.1 按刷新方式分类

类型	说明
完全刷新	重新执行物化视图定义的查询，覆盖所有数据（REFRESH COMPLETE）
快速刷新	仅更新自上次刷新后变化的数据（REFRESH FAST），需依赖物化视图日志
强制刷新	优先尝试快速刷新，失败则自动执行完全刷新（REFRESH FORCE，默认方式）
从未刷新	创建后不自动刷新，需手动触发（REFRESH NEVER）

3.2 按刷新时机分类

类型	说明
定时刷新	按预设时间间隔自动刷新（通过 START WITH 和 NEXT 定义）
按需刷新	手动执行 DBMS_MVIEW.REFRESH 过程触发刷新
实时刷新	源表数据变更时自动同步（仅适用于可更新物化视图，需配置 ON COMMIT）

3.3 特殊类型

• 可更新物化视图（Updatable MV）：允许直接对物化视图执行 DML 操作，变更可同步回源表（需配置复制环境）
• 分区物化视图（Partitioned MV）：按分区键拆分数据，支持分区级刷新，适合超大型数据集
• 仅刷新物化视图（Refresh-Only MV）：不支持查询重写，仅用于数据同步场景

四、创建物化视图

4.1 基础语法

sql

CREATE MATERIALIZED VIEW [schema.]materialized_view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
[REFRESH [FAST | COMPLETE | FORCE | NEVER]
  [ON DEMAND | ON COMMIT]
  [START WITH date] [NEXT date]
  [WITH PRIMARY KEY | WITH ROWID]
]
[ENABLE | DISABLE QUERY REWRITE]
AS
<query_definition>;  -- 定义物化视图数据来源的查询（SELECT语句）

4.2 关键参数说明

• BUILD IMMEDIATE：创建时立即生成数据（默认）
• BUILD DEFERRED：创建时不生成数据，首次刷新后才有数据
• REFRESH：指定刷新方式（FAST/COMPLETE/FORCE/NEVER）
• ON DEMAND：按需刷新（默认），需手动或定时触发
• ON COMMIT：源表提交事务时自动刷新（仅支持快速刷新）
• START WITH：首次自动刷新时间（如 TO_DATE('2023-10-01 02:00:00','YYYY-MM-DD HH24:MI:SS')）
• NEXT：后续刷新间隔（如 SYSDATE + 1 表示每天刷新）
• WITH PRIMARY KEY：基于主键刷新（默认，推荐）
• WITH ROWID：基于 ROWID 刷新（源表无主键时使用）
• ENABLE QUERY REWRITE：允许优化器使用物化视图重写查询（提升性能）

4.3 示例

示例 1：创建定时完全刷新的物化视图

sql

-- 创建按天完全刷新的销售汇总物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('2023-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
NEXT SYSDATE + 1
ENABLE QUERY REWRITE
AS
SELECT 
  region_id, 
  SUM(amount) AS total_amount, 
  COUNT(order_id) AS order_count,
  TRUNC(order_date) AS order_day
FROM sales
GROUP BY region_id, TRUNC(order_date);

示例 2：创建基于物化视图日志的快速刷新物化视图

sql

-- 1. 为源表创建物化视图日志（支持快速刷新）
CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY, ROWID (amount, order_date)
INCLUDING NEW VALUES;

-- 2. 创建快速刷新的物化视图
CREATE MATERIALIZED VIEW mv_sales_fast
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT 
  region_id, 
  SUM(amount) AS total_amount,
  TRUNC(order_date) AS order_day
FROM sales
GROUP BY region_id, TRUNC(order_date);

示例 3：创建实时刷新（ON COMMIT）的物化视图

sql

-- 1. 源表创建物化视图日志（含新值）
CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY, INCLUDING NEW VALUES;

-- 2. 创建提交时自动刷新的物化视图
CREATE MATERIALIZED VIEW mv_emp_dept
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

五、物化视图日志

5.1 作用

物化视图日志是源表上的辅助对象，记录源表的 DML 操作（插入、更新、删除），使物化视图能快速刷新（仅同步变更数据，无需全量重算）。

5.2 创建语法

sql

CREATE MATERIALIZED VIEW LOG ON [schema.]table_name
[WITH {PRIMARY KEY | ROWID | OBJECT ID} [, column_list]]
[INCLUDING NEW VALUES | EXCLUDING NEW VALUES]
[FOR {ALL | DML | REFRESH} OPERATIONS]
[TABLESPACE tablespace_name];

5.3 关键参数

• WITH PRIMARY KEY：记录主键列（推荐，源表需有主键）
• WITH ROWID：记录行的 ROWID（源表无主键时使用）
• INCLUDING NEW VALUES：同时记录修改前和修改后的值（更新操作需要）
• EXCLUDING NEW VALUES：仅记录修改前的值（默认）

5.4 示例

sql

-- 为含主键的表创建物化视图日志（支持更新操作）
CREATE MATERIALIZED VIEW LOG ON orders
WITH PRIMARY KEY, ROWID (order_status, total_amount)
INCLUDING NEW VALUES
TABLESPACE mv_log_ts;

六、刷新操作

6.1 手动刷新

使用 DBMS_MVIEW.REFRESH 过程手动触发刷新：

plsql

-- 刷新单个物化视图（快速刷新）
BEGIN
  DBMS_MVIEW.REFRESH(
    list => 'mv_sales_fast',  -- 物化视图名
    method => 'F'  -- F=快速刷新, C=完全刷新, ?=强制刷新
  );
END;
/

-- 批量刷新多个物化视图
BEGIN
  DBMS_MVIEW.REFRESH(
    list => 'mv_sales_summary, mv_emp_dept',
    method => 'C'  -- 完全刷新
  );
END;
/

6.2 自动刷新

通过 START WITH 和 NEXT 配置定时自动刷新（创建时指定或后期修改）：

sql

-- 修改物化视图为每小时自动刷新
ALTER MATERIALIZED VIEW mv_sales_fast
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/24;

6.3 刷新状态查询

sql

-- 查看物化视图信息（最后刷新时间、下次刷新时间等）
SELECT 
  mview_name, 
  last_refresh_type, 
  last_refresh_date,
  next_refresh_date,
  refresh_mode,
  refresh_method
FROM user_mviews;

七、查询重写（Query Rewrite）

7.1 作用

查询重写是 Oracle 优化器的特性，当用户查询与物化视图定义匹配时，优化器自动使用物化视图数据替代源表查询，大幅提升性能。

7.2 启用条件

• 物化视图需指定 ENABLE QUERY REWRITE
• 会话参数 QUERY_REWRITE_ENABLED 需设为 TRUE（默认）：

  sql

  ALTER SESSION SET query_rewrite_enabled = TRUE;
  

• 物化视图与查询的聚合逻辑、过滤条件需兼容

7.3 验证重写是否生效

使用 EXPLAIN PLAN 查看执行计划：

sql

EXPLAIN PLAN FOR
SELECT region_id, SUM(amount) 
FROM sales 
WHERE order_date >= TRUNC(SYSDATE) - 7
GROUP BY region_id;

-- 查看执行计划（若出现"MATERIALIZED VIEW"则重写生效）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

八、管理与维护

8.1 修改物化视图

sql

-- 修改刷新方式
ALTER MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON DEMAND;

-- 启用/禁用查询重写
ALTER MATERIALIZED VIEW mv_sales_summary
DISABLE QUERY REWRITE;

-- 修改存储参数
ALTER MATERIALIZED VIEW mv_sales_summary
STORAGE (INITIAL 10M NEXT 5M);

8.2 删除物化视图

sql

DROP MATERIALIZED VIEW mv_sales_summary;

8.3 重建物化视图

当源表结构变更（如新增列）后，需重建物化视图：

sql

-- 先删除旧物化视图
DROP MATERIALIZED VIEW mv_sales_summary;

-- 重新创建（包含新列或调整逻辑）
CREATE MATERIALIZED VIEW mv_sales_summary
...;  -- 新的定义

8.4 统计信息收集

为确保优化器正确选择查询重写，需定期收集物化视图的统计信息：

sql

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'MV_SALES_SUMMARY',
    estimate_percent => 100,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

九、性能优化

9.1 索引优化

• 为物化视图的过滤列、连接列创建索引（如 region_id、order_day）
• 对聚合物化视图，为聚合列 + 分组列创建组合索引：

  sql

  CREATE INDEX idx_mv_sales_region_day ON mv_sales_summary(region_id, order_day);
  

9.2 分区策略

• 对超大型物化视图，使用分区（与源表分区键一致）：

  sql

  CREATE MATERIALIZED VIEW mv_sales_partitioned
  PARTITION BY RANGE (order_day) (
    PARTITION p202301 VALUES LESS THAN (TO_DATE('2023-02-01','YYYY-MM-DD')),
    PARTITION p202302 VALUES LESS THAN (TO_DATE('2023-03-01','YYYY-MM-DD'))
  )
  REFRESH COMPLETE
  AS
  SELECT region_id, SUM(amount) AS total_amount, TRUNC(order_date) AS order_day
  FROM sales
  GROUP BY region_id, TRUNC(order_date);
  

9.3 刷新策略优化

• 频繁变更的表：使用 FAST REFRESH ON COMMIT（需评估源表性能影响）
• 非实时需求的报表：使用定时刷新（如凌晨低峰期）
• 超大型表：拆分多个小物化视图（按业务域或时间范围）

9.4 存储优化

• 将物化视图与源表存储在不同表空间，分散 I/O 压力
• 启用压缩（COMPRESS FOR QUERY）：

  sql

  CREATE MATERIALIZED VIEW mv_sales_compressed
  COMPRESS FOR QUERY
  AS ...;
  

十、典型场景示例

10.1 数据仓库汇总场景

为数据仓库创建按日、按区域的销售汇总物化视图，加速报表查询：

sql

-- 创建按日汇总的物化视图（每日凌晨2点刷新）
CREATE MATERIALIZED VIEW mv_daily_sales
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('2023-10-01 02:00:00', 'YYYY-MM-DD HH24:MI:SS')
NEXT SYSDATE + 1
ENABLE QUERY REWRITE
AS
SELECT 
  region_id, 
  product_id,
  TRUNC(order_date) AS sale_date,
  SUM(quantity) AS total_qty,
  SUM(amount) AS total_amount
FROM sales_fact
GROUP BY region_id, product_id, TRUNC(order_date);

10.2 分布式数据同步场景

通过物化视图同步远程库的产品信息到本地：

sql

-- 1. 创建数据库链接
CREATE DATABASE LINK remote_erp
CONNECT TO erp_user IDENTIFIED BY "password"
USING '//192.168.1.100:1521/ERPDB';

-- 2. 创建同步远程产品表的物化视图（每小时刷新）
CREATE MATERIALIZED VIEW mv_remote_products
REFRESH FAST ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
AS
SELECT product_id, product_name, price, status
FROM products@remote_erp;

10.3 查询重写优化场景

创建客户订单数物化视图，优化频繁执行的统计查询：

sql

-- 1. 创建物化视图
CREATE MATERIALIZED VIEW mv_customer_orders
ENABLE QUERY REWRITE
AS
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;

-- 2. 用户查询（优化器自动重写为访问物化视图）
SELECT customer_id, COUNT(order_id) 
FROM orders 
GROUP BY customer_id;

十一、最佳实践与注意事项

1. 权限控制

   • 最小化物化视图用户权限，仅授予必要的 SELECT 和刷新权限
   • 对敏感数据的物化视图，通过 GRANT SELECT 严格控制访问

2. 刷新时机选择

   • 核心业务高峰期避免刷新大型物化视图
   • ON COMMIT 刷新会增加源表提交开销，谨慎用于高频更新表

3. 避免过度使用

   • 不建议为简单查询创建物化视图（维护成本 > 性能收益）
   • 定期清理不再使用的物化视图及对应的日志

4. 监控与告警

   • 监控物化视图刷新成功率（通过 user_mviews.last_refresh_date）
   • 对刷新失败的物化视图设置告警（结合 Oracle AWR/ASH 或监控工具）

5. 与分区表配合

   • 源表为分区表时，物化视图建议采用相同分区策略，支持分区级刷新
   • 使用 DBMS_MVIEW.REFRESH_PARTITION 单独刷新特定分区：

     plsql

     BEGIN
       DBMS_MVIEW.REFRESH_PARTITION(
         mview_name => 'mv_sales_partitioned',
         partition_name => 'p202301',
         method => 'C'
       );
     END;
     /
     

十二、总结

Oracle 19c 物化视图通过物理存储预计算结果，有效提升复杂查询性能，是数据仓库、报表系统和分布式数据同步的核心工具。合理设计物化视图的刷新策略、索引和分区，结合查询重写特性，可显著降低系统负载并提升用户体验。

实际开发中，需根据业务场景平衡实时性与性能，定期维护物化视图及其日志，确保其持续有效发挥作用。