Oracle 物化视图性能优化

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

物化视图（Materialized View） 预计算结果，加速查询[1]：

特点：

物理存储
自动刷新
Query Rewrite

2. 创建物化视图

2.1 基本创建

CREATE MATERIALIZED VIEW mv_sales_by_dept
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
AS
SELECT 
  dept_id,
  SUM(amount) AS total_sales,
  COUNT(*) AS cnt
FROM sales
GROUP BY dept_id;

2.2 刷新方式

方式	说明
ON COMMIT	提交刷新
ON DEMAND	按需刷新
START WITH	定时
NEXT	周期

2.3 刷新类型

类型	说明
COMPLETE	全部刷新
FAST	增量刷新
FORCE	优先 FAST
NEVER	不刷新

3. FAST Refresh

3.1 物化视图日志

CREATE MATERIALIZED VIEW LOG ON sales 
  WITH ROWID, SEQUENCE (dept_id, amount)
  INCLUDING NEW VALUES;

3.2 限制

必须 MV 日志
特定聚合支持
限制条件多

3.3 验证

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_SALES_BY_DEPT');

SELECT capability_name, possible, msgtxt
FROM mv_capabilities_table
WHERE mvname = 'MV_SALES_BY_DEPT';

4. Query Rewrite

4.1 启用

ALTER SYSTEM SET query_rewrite_enabled = TRUE;
ALTER SYSTEM SET query_rewrite_integrity = enforced;
-- enforced / trusted / stale_tolerated

4.2 HINT

SELECT /*+ REWRITE(mv_sales) */ SUM(amount) FROM sales;
SELECT /*+ NO_REWRITE */ SUM(amount) FROM sales;

4.3 验证

EXPLAIN PLAN FOR SELECT SUM(amount) FROM sales;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- MAT_VIEW REWRITE ACCESS

5. 调度刷新

5.1 自动

CREATE MATERIALIZED VIEW mv_sales
  REFRESH COMPLETE
  START WITH SYSDATE
  NEXT SYSDATE + 1  -- 每天
AS SELECT ...;

5.2 手动

EXEC DBMS_MVIEW.REFRESH('MV_SALES_BY_DEPT', 'C');  -- Complete
EXEC DBMS_MVIEW.REFRESH('MV_SALES_BY_DEPT', 'F');  -- Fast
EXEC DBMS_MVIEW.REFRESH('MV_SALES_BY_DEPT', '?');  -- Force

-- 多个
EXEC DBMS_MVIEW.REFRESH('MV1,MV2,MV3', 'C');

5.3 并行刷新

EXEC DBMS_MVIEW.REFRESH(
  list => 'MV_SALES_BY_DEPT',
  method => 'C',
  parallelism => 8
);

6. 刷新组

6.1 创建

EXEC DBMS_REFRESH.MAKE(
  name => 'refresh_group_1',
  list => 'MV1, MV2, MV3',
  next_date => SYSDATE + 1,
  interval => 'SYSDATE + 1'
);

6.2 刷新

EXEC DBMS_REFRESH.REFRESH('refresh_group_1');

7. 类型

7.1 聚合物化视图

CREATE MATERIALIZED VIEW mv_sum AS
SELECT dept_id, SUM(salary) FROM emp GROUP BY dept_id;

7.2 JOIN 物化视图

CREATE MATERIALIZED VIEW mv_join AS
SELECT e.id, e.name, d.dept_name
FROM emp e, dept d WHERE e.dept_id = d.id;

7.3 嵌套物化视图

CREATE MATERIALIZED VIEW mv_nested AS
SELECT * FROM mv_sales_by_dept WHERE total_sales > 1000;

8. 索引

8.1 创建索引

CREATE INDEX idx_mv_dept ON mv_sales_by_dept(dept_id);

8.2 优势

加速 MV 查询
提升 Query Rewrite

9. 性能优化

9.1 PCT Refresh

-- Partition Change Tracking
-- 仅刷新变化分区
CREATE MATERIALIZED VIEW mv_sales
  REFRESH FAST ON DEMAND
  WITH PRIMARY KEY
  USING TRUSTED CONSTRAINTS
AS SELECT ... FROM sales PARTITION(...);

9.2 并行

CREATE MATERIALIZED VIEW mv_sales PARALLEL 8 AS SELECT ...;

9.3 压缩

CREATE MATERIALIZED VIEW mv_sales COMPRESS AS SELECT ...;

10. 监控

10.1 状态

SELECT 
  owner, 
  mview_name,
  last_refresh_type,
  last_refresh_date,
  compile_state
FROM dba_mviews;

10.2 刷新历史

SELECT * FROM dba_mview_refresh_times;

10.3 Query Rewrite

SELECT name, value FROM v$sysstat WHERE name LIKE '%rewrite%';

11. 常见坑与排错

11.1 FAST 刷新失败

-- 1. 检查 MV 日志
SELECT * FROM user_mview_logs;

-- 2. 验证能力
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_NAME');

-- 3. COMPLETE 刷新

11.2 Query Rewrite 不生效

-- 1. 参数
SHOW PARAMETER query_rewrite_enabled

-- 2. ENABLE QUERY REWRITE
-- 3. 统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

11.3 刷新慢

-- 1. 并行
-- 2. PCT
-- 3. 增大 PGA

12. 最佳实践

聚合 MV：报表加速
FAST Refresh：增量
Query Rewrite：透明加速
MV 日志：FAST 前提
刷新组：一致性
PCT：分区表
并行刷新：性能
收集统计：CBO
监控刷新：状态
定期 COMPLETE：修复

13. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “Materialized Views” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/materialized-views.html