Oracle物化视图解析

Oracle物化视图(Materialized View)是一种特殊类型的数据库对象，它预先计算并存储复杂查询的结果，从而可以显著提高某些查询的性能。物化视图可以被视为一种缓存机制，其中包含的数据是基于一个或多个基表的查询结果的副本。与普通的视图不同，物化视图中的数据是物理存储的，而不是每次查询时重新计算。

一、物化视图的优点

1. 提高查询性能：预计算的数据可以快速检索，避免了实时计算的开销。
2. 降低网络负载：如果数据是从远程数据库提取的，物化视图可以减少网络传输量。
3. 简化查询：用户可以直接查询物化视图，而无需关心底层的数据结构。
4. 数据汇总：物化视图可以包含聚合数据，如SUM、AVG等，加速汇总查询。

二、物化视图的缺点

1. 存储成本：物化视图占用额外的磁盘空间。
2. 维护成本：物化视图需要定期刷新以保持数据最新。
3. 数据延迟：数据可能不是实时的，取决于刷新频率。

三、物化视图的类型

1. 简单物化视图：基于单个基表的物化视图。
2. 连接物化视图：基于多个基表的连接查询的物化视图。
3. 汇总物化视图：包含聚合函数结果的物化视图。

四、创建物化视图

创建物化视图的基本语法如下：

CREATE MATERIALIZED VIEW mv_name
   BUILD {IMMEDIATE | DEFERRED}
   REFRESH {FAST | COMPLETE | FORCE} ON {COMMIT | STATEMENT | ROW | DEMAND | NEVER}
   ENABLE QUERY REWRITE
   AS SELECT column_list FROM table_name WHERE condition;

• BUILD IMMEDIATE 或 DEFERRED：决定物化视图在创建时是否立即构建。
• REFRESH FAST, COMPLETE, FORCE：定义物化视图的刷新方式。
• ON COMMIT, STATEMENT, ROW, DEMAND, NEVER：定义何时触发物化视图的刷新。
• ENABLE QUERY REWRITE：允许Oracle自动重写查询，直接从物化视图中获取数据。

五、刷新物化视图

刷新物化视图可以使用以下命令：

BEGIN
   DBMS_MVIEW.REFRESH('mv_name', 'C');
END;
/

其中 'C' 表示完全刷新（Complete），也可以用 'F' 表示快速刷新（Fast）。

1、手动刷新

手动刷新物化视图通常用于那些不需要频繁更新的视图。你可以使用PL/SQL包DBMS_MVIEW中的REFRESH过程来刷新物化视图。

BEGIN
   DBMS_MVIEW.REFRESH(
      materialized_view_name => 'YOUR_MV_NAME',
      method                  => 'C', -- 'C' for complete refresh, 'F' for fast refresh
      job_queue               => TRUE);
END;
/

2、自动刷新

自动刷新可以通过设定刷新策略来实现，这样物化视图会在特定条件下自动更新。

（1）刷新策略

在创建物化视图时，你可以指定不同的刷新策略：

• ON COMMIT: 每次对基表的修改提交后，立即刷新物化视图。
• ON STATEMENT: 每次执行影响基表的SQL语句后，刷新物化视图。
• ON ROW: 当基表中发生行级别的更改时刷新物化视图。
• ON DEMAND: 不自动刷新，需要手动触发。
• NEVER: 从不自动刷新，总是需要手动刷新。

（2）定期刷新

你还可以设置物化视图在固定时间间隔内刷新，例如：

CREATE MATERIALIZED VIEW mv_name
   REFRESH COMPLETE ON DEMAND
   START WITH SYSTIMESTAMP + INTERVAL '1' HOUR
   NEXT SYSTIMESTAMP + INTERVAL '1' HOUR
   ENABLE QUERY REWRITE
   AS SELECT ... ;

在这个例子中，物化视图将在第一次被创建后一小时开始，并且之后每小时刷新一次。

（3）快速刷新

物化视图的快速刷新（Fast Refresh）是一种高效的更新方式，用于在物化视图中只应用自上次刷新以来对基表所做的更改。这种刷新机制依赖于物化视图日志（Materialized View Log），该日志记录了基表上所有相关的DML（数据操纵语言）操作，如INSERT、UPDATE和DELETE。

3.1、快速刷新过程

快速刷新过程大致如下：

1. 读取物化视图日志：Oracle数据库首先读取物化视图日志，以确定基表中自上次刷新以来所有修改过的行。
2. 分析更改：系统分析日志中的每一项更改，确定哪些行需要在物化视图中进行插入、更新或删除操作。
3. 应用更改：根据分析结果，系统将只对物化视图中相应的行进行必要的更新。这意味着只有那些真正需要刷新的数据才会被处理，而其他未改动的数据保持不变。
4. 清除日志：完成刷新后，系统会清除已处理的日志条目，准备记录下一批更改。

3.2、快速刷新的优点

• 效率高：相比完全刷新，快速刷新只处理自上次刷新以来的更改，大大减少了处理的数据量，节省了时间和系统资源。
• 数据一致性：能够更频繁地刷新物化视图，保证物化视图数据与基表数据的一致性，同时对系统的实时影响较小。

3.3、使用限制

要使物化视图支持快速刷新，必须满足以下条件：

• 基表必须有物化视图日志。
• 物化视图必须基于一个确定性的查询（即相同的输入总是产生相同的结果）。
• 物化视图的定义不能包含聚合函数、GROUP BY子句或连接多个表（除非连接的其他表也有相应的物化视图日志）。

3.4、执行快速刷新

快速刷新可以通过执行REFRESH语句来触发，如下所示：

REFRESH MATERIALIZED VIEW materialized_view_name;

如果物化视图被配置为支持快速刷新，那么上述语句将自动执行快速刷新。

此外，还可以通过ALTER MATERIALIZED VIEW语句来启用或禁用快速刷新，例如：

ALTER MATERIALIZED VIEW materialized_view_name FAST REFRESH;

快速刷新是管理大型、复杂物化视图的有效手段，特别是在需要频繁更新且系统资源有限的情况下。通过精心设计和维护物化视图及其日志，可以显著提高数据仓库和报表应用的性能和效率。

六、查询重写

物化视图的查询重写是Oracle数据库中一项重要的优化技术，用于提高查询性能。这项技术允许数据库优化器在执行查询时，如果存在一个与查询相匹配的物化视图，那么可以重写原始查询，使用物化视图中已经预计算好的数据，而不是直接从基表中检索数据。这可以极大地减少查询的执行时间和I/O操作，尤其是在复杂的分析查询中。

1、查询重写机制

查询重写由数据库优化器完成，当优化器遇到一个查询时，它会检查是否有与这个查询相匹配的物化视图。如果有，优化器会评估使用物化视图与直接从基表执行查询的成本，并选择成本更低的方法来执行查询。这个过程称为查询重写。

2、查询重写的条件

查询重写发生需要满足以下条件：

1. 物化视图必须启用查询重写：在创建物化视图时，必须指定ENABLE QUERY REWRITE选项。

2. 查询必须与物化视图的定义兼容：这意味着查询的列、筛选条件、排序、分组等必须与物化视图的定义一致。

3. 成本评估：优化器会比较使用物化视图和直接从基表执行查询的成本，只有当使用物化视图的成本更低时，才会进行查询重写。

3、查询重写的限制

尽管查询重写可以显著提高性能，但也有一定的限制：

• 物化视图可能不是实时的：物化视图的数据可能不是最新的，因为它需要定期刷新才能反映基表的变化。
• 物化视图可能无法覆盖所有查询：不是所有的查询都能找到匹配的物化视图，特别是那些包含动态过滤条件或非常特定的查询。
• 物化视图的存储成本：存储物化视图需要额外的磁盘空间，且刷新物化视图可能会消耗计算资源。

4、查询重写示例

假设有一个物化视图mv_sales_summary，它包含了sales表的每月销售总额。当执行如下查询时：

SELECT SUM(amount) FROM sales WHERE EXTRACT(MONTH FROM sale_date) = 1;

如果mv_sales_summary已经存在并且包含每个月的销售总额，那么优化器可能会重写上述查询为：

SELECT amount FROM mv_sales_summary WHERE month = 1;

这将避免对sales表的扫描，而直接从mv_sales_summary中获取结果，大大提高了查询效率。

七、物化视图日志

物化视图日志（Materialized View Log）是Oracle数据库中一种特殊类型的日志，它被用来支持物化视图的快速刷新（Fast Refresh）。当物化视图需要更新时，快速刷新机制可以仅基于物化视图日志中的变更记录来更新物化视图，而不是重新计算整个视图，从而大大减少了刷新所需的时间和资源。

1、物化视图日志的作用

物化视图日志记录了其关联的基表（Master Table）上的所有更改。每当基表上发生插入、更新或删除操作时，相应的更改就会被记录在物化视图日志中。这些记录包括行的标识信息，如ROWID或主键值，以及修改前后的数据值（如果是包含新值的物化视图日志）。

2、物化视图日志的类型

物化视图日志可以有几种不同的类型：

• ROWID类型的日志：这种类型的日志只记录基表中每行的ROWID。ROWID是Oracle数据库中一种特殊的行标识符，可以直接定位到数据库块中的特定行。

• 主键（PRIMARY KEY）类型的日志：这种类型的日志记录了基表中每行的主键值，通常用于没有ROWID的表，或者当表被分区时。

• 包含新值（INCLUDING NEW VALUES）的物化视图日志：这种类型的日志除了行标识外，还会记录修改后的值，使得在刷新时可以只应用更改而不必重新读取整个基表。

3、创建物化视图日志

创建物化视图日志需要在基表上执行一个CREATE MATERIALIZED VIEW LOG语句。例如：

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

在这个例子中，sales表上的物化视图日志被创建，它包括ROWID、序列order_id以及新值。

4、使用物化视图日志进行快速刷新

要使用物化视图日志进行快速刷新，物化视图在创建时必须指定FAST REFRESH选项。此外，物化视图的定义应该能够从基表和物化视图日志中重建出来。快速刷新机制会分析物化视图日志中的更改记录，然后仅更新物化视图中受影响的部分。

4、限制与注意事项

• 物化视图日志不能在临时表或视图上创建。
• 如果基表上的事务并发度很高，物化视图日志可能会变得非常大，因此需要考虑存储需求。
• 物化视图日志的维护和刷新操作可能会影响数据库的性能，特别是在高负载环境下。