28.Oracle11g物化视图

oracle基础系统学习目录

01.CentOS7静默安装oracle11g
02.Oracle的启动过程
03.从简单的sql开始
04.Oracle的体系架构
05.Oracle数据库对象
06.Oracle数据备份与恢复
07.用户和权限管理
08.Oracle的表
09.Oracle表的分区
10.Oracle的同义词与序列
11.Oracle的视图
12.Oracle的索引
13.Oracle通过JDBC连接Java
14.Oracle中的事务
15.Oracle11g的归档方式和日志文件的相关操作
16.Oracle的数据字典和动态性能视图
17.Oracle11g的PL/SQL基础
18.Oracle的过程和函数
19.Oracle11g中的游标
20.Oracle11g中的触发器
21.Oracle的程序包(Package)
22.Oracle中的临时表空间
23.Oracle11g的UNDO表空间
24.Oracle11g的逻辑备份与恢复
25. Oracle的回收站
26.Oracle11g的数据装载
27.Oracle11g的闪回Flashback
28.Oracle11g物化视图

---

一、物化视图（Materialized View）概述

1、问题的提出

  在分布式的海量数据环境中，信息查询的速度问题显得尤为重要。传统的查询方式，即根据用户的要求，每次都重新的进入基表或视图查询，所需的时间太长。例如移动通信行业，即使客户需要查询很少的信息，也可能会花费很多时间，可能在30分钟左右，如果数据库主机稍有繁忙，这个时间会更长，客户难以忍受。为了解决这种问题，ORACLE中设计了物化视图（又称为MV）。

2、什么是物化视图

  Oracle 11g中的物化视图（Materialized View）是一个预先计算的结果集，存储在数据库中，以便在需要时可以快速检索。物化视图可以包含聚合函数、连接和过滤条件，可以提高查询性能，减少数据检索的时间。

  物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。

  物化视图可以查询表，视图和其它的物化视图。

  通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。

  对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

  对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

  物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

3、物化视图的作用

  Oracle 11g物化视图是一种预先计算和存储的查询结果，可以提高查询性能和减少数据库负载。物化视图可以存储查询结果并定期刷新，以确保数据的实时性。它可以在查询时直接使用预先计算的结果，而不需要重新执行复杂的查询，从而提高查询性能。

具体来说，Oracle 11g物化视图的作用包括：

1. 提高查询性能：物化视图可以存储预先计算的结果，当用户查询相同的数据时，可以直接使用物化视图中的结果，而不需要重新执行查询，从而提高查询性能。
2. 减少数据库负载：通过存储预先计算的结果，物化视图可以减少数据库的负载，避免重复执行复杂查询对数据库的压力。
3. 提供实时数据：物化视图可以定期刷新，确保数据的实时性，用户可以随时查询到最新的数据。

  一个具体的案例是，在一个销售管理系统中，需要频繁查询每个产品的销售总额和数量。为了提高查询性能，可以创建一个物化视图来存储每个产品的销售总额和数量的预先计算结果。当用户需要查询某个产品的销售情况时，可以直接使用物化视图中的结果，而不需要重新计算每次查询。同时，可以定期刷新物化视图，确保查询结果的实时性。这样可以显著提高查询性能，并减少数据库负载。

4、物化视图的优点

1. 提高查询性能：物化视图存储了预先计算的结果集，可以减少查询时的计算时间。
2. 减少数据检索时间：物化视图中存储了部分或全部的原始数据，可以减少数据检索的时间。
3. 支持离线分析：物化视图可以存储历史数据，支持离线分析和报表生成。

二、物化视图的使用

1、创建语法

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

CREATE MATERIALIZED VIEW mv_name
BUILD [IMMEDIATE|DEFERRED]
REFRESH [FAST|COMPLETE|FORCE|ON COMMIT]
AS
SELECT ...
FROM ...
[WHERE ...]

其中，mv_name是物化视图的名称，BUILD子句指定物化视图的创建方式（IMMEDIATE表示立即创建，DEFERRED表示延迟创建），REFRESH子句指定物化视图的刷新方式（FAST表示只刷新修改过的数据，COMPLETE表示完全刷新，FORCE表示尝试使用增量刷新，ON COMMIT表示在每次提交时刷新）。

• 如果创建基于主键的物化视图，则必须具有访问主表、访问主表的日志、create MATERIALIZED VIEW这三个权限。

• 如果创建基于rowid的物化视图，则必须具有访问主表、create MATERIALIZED VIEW这两个权限。

• 物化视图的刷新可以通过以下方式进行：

  1. 手动刷新：使用DBMS_MVIEW.REFRESH过程手动刷新物化视图。
  2. 定时刷新：使用DBMS_SCHEDULER或者DBMS_JOB来定时刷新物化视图。
  3. 增量刷新：使用FAST刷新方式，只刷新修改过的数据。

• 物化视图的使用需要考虑以下几点：

  1. 物化视图需要占用存储空间，需要根据实际情况进行合理的管理。
  2. 物化视图需要及时刷新，否则会导致数据不一致的问题。
  3. 物化视图的选择需要根据实际查询需求进行合理的设计，避免过度使用物化视图导致性能下降。

总之，物化视图是Oracle 11g中一个非常有用的功能，可以提高查询性能，减少数据检索时间，支持离线分析和报表生成。在使用时需要合理设计和管理，才能发挥其最大的作用。

2、物化视图创建时的选项

1. 查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果。默认为DISABLE QUERY REWRITE。

   CREATE MATERIALIZED VIEW mv_sales
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT product_id, SUM(amount_sold) total_sales
   FROM sales
   GROUP BY product_id;
   

   在上面的示例中，我们创建了一个名为mv_sales的物化视图，通过ENABLE QUERY REWRITE选项启用了查询重写功能。

2. 物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。

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

   在上面的示例中，我们为sales表创建了一个物化视图日志，指定了ROWID类型，并包含了product_id和amount_sold列。

3. 刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。

   • FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
   • COMPLETE刷新对整个物化视图进行完全的刷新。
   • 如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。
   • NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。

   CREATE MATERIALIZED VIEW mv_sales
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT product_id, SUM(amount_sold) total_sales
   FROM sales
   GROUP BY product_id;
   

   在上面的示例中，我们创建了一个名为mv_sales的物化视图，通过REFRESH FAST ON COMMIT选项指定了在提交时快速刷新物化视图。

这些示例展示了创建物化视图时的查询重写、物化视图日志和刷新选项的用法。根据实际需求，可以根据这些选项进行灵活的设置。

3、oracle11g的mlog$_视图和rupd$_视图的作用及相关操作

在 Oracle 11g 中，mlog$_ 视图和 rupd$_ 视图是与物化视图日志（Materialized View Logs）相关的系统视图，用于支持物化视图的快速刷新和增量刷新。

1. mlog$_ 视图：

   • mlog$_ 视图是物化视图日志的基本视图，用于记录表中发生的变化，以便在刷新物化视图时进行增量刷新。每个物化视图日志都对应着一个基表，并且会记录基表中发生的 INSERT、UPDATE 和 DELETE 操作。

   • 操作 mlog$_ 视图的主要目的是为了管理物化视图的刷新。通过 mlog$_ 视图，可以了解基表中的数据变化情况，从而在刷新物化视图时仅刷新发生变化的数据，提高刷新效率。

2. rupd$_ 视图：

   • rupd$_ 视图是用于记录基表中的行级别更新操作的视图。当对基表进行 UPDATE 操作时，Oracle 会将更新前的数据记录在 rupd$_ 视图中，以便在刷新物化视图时能够根据更新前的数据计算出正确的增量更新数据。

   • 操作 rupd$_ 视图的主要目的是为了支持物化视图的快速刷新。通过 rupd$_ 视图，可以跟踪基表中的行级别更新操作，确保在刷新物化视图时能够正确地计算出增量更新数据。

相关操作：

1. 创建物化视图日志：

   CREATE MATERIALIZED VIEW LOG ON table_name
   WITH ROWID, SEQUENCE (column1, column2)
   INCLUDING NEW VALUES;
   

   通过以上语句可以创建物化视图日志，指定了记录变化的方式和需要记录的列。

2. 刷新物化视图：

   DBMS_MVIEW.REFRESH('materialized_view_name', 'F');
   

   通过以上语句可以使用 DBMS_MVIEW 包中的 REFRESH 过程来刷新物化视图，‘F’ 表示使用快速刷新。

3. 查询物化视图日志和 rupd$_ 视图：

   SELECT * FROM mlog$_table_name;
   SELECT * FROM rupd$_table_name;
   

   通过以上语句可以查询物化视图日志和 rupd$_ 视图中的数据，了解基表的变化情况和行级别更新操作。

总之，mlog$_ 视图和 rupd$_ 视图是在 Oracle 11g 中用于支持物化视图的快速刷新和增量刷新的重要系统视图，通过这些视图可以管理物化视图的刷新和跟踪基表的变化情况。

4、普通用户创建物化视图需要赋予的权限

当普通用户需要创建物化视图时，需要具有以下权限：

1. CREATE TABLE 权限：普通用户需要有创建表的权限，因为物化视图实际上是一个表的数据快照。

   GRANT CREATE TABLE TO <user_name>;
   

2. CREATE MATERIALIZED VIEW 权限：普通用户需要有创建物化视图的权限。

   GRANT CREATE MATERIALIZED VIEW TO <user_name>;
   

3. 相关表的 SELECT 权限：如果物化视图是基于其他表的数据构建的，普通用户需要有这些表的 SELECT 权限。

   GRANT SELECT ON <table_name> TO <user_name>;
   

4. 刷新物化视图的权限（可选）：如果普通用户需要刷新物化视图，需要具有刷新物化视图的权限。

   GRANT ALTER ANY MATERIALIZED VIEW TO <user_name>;
   

通过以上权限的授予，普通用户就可以创建和管理自己的物化视图。需要注意的是，赋予 ALTER ANY MATERIALIZED VIEW 权限可以让用户刷新所有物化视图，这是一个较高级别的权限，需要谨慎授予。

三、物化视图具体使用案例

1、基于主键的物化视图案例

  假设我们有一个销售订单系统，其中包含以下两个表：orders（订单信息）和 order_items（订单明细信息）。orders表中包含了订单的主要信息，包括订单号（order_id）作为主键，订单日期（order_date）等；order_items表中包含了订单的明细信息，包括订单号（order_id）和产品号（product_id）等。

  现在我们希望创建一个基于主键的物化视图，用于统计每个订单的总金额。

创建一个基于主键的物化视图，前提条件远程表必须具有主键

下面是具体的创建代码：

1. 首先，创建orders表和order_items表：

   CREATE TABLE orders (
       order_id NUMBER PRIMARY KEY,
       order_date DATE
   );
   
   CREATE TABLE order_items (
       order_id NUMBER,
       product_id NUMBER,
       quantity NUMBER,
       price NUMBER,
       PRIMARY KEY (order_id, product_id)
   );
   

2. 然后，创建物化视图日志，以支持快速刷新：

   CREATE MATERIALIZED VIEW LOG ON orders
   WITH PRIMARY KEY, SEQUENCE (order_id, order_date);
   
   CREATE MATERIALIZED VIEW LOG ON order_items
   WITH PRIMARY KEY, SEQUENCE (order_id, product_id, quantity, price)
   INCLUDING NEW VALUES;
   

3. 接下来，创建基于主键的物化视图，用于统计每个订单的总金额：

   CREATE MATERIALIZED VIEW mv_order_total
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT o.order_id, o.order_date, SUM(oi.quantity * oi.price) AS total_amount
   FROM orders o
   JOIN order_items oi ON o.order_id = oi.order_id
   GROUP BY o.order_id, o.order_date;
   

   在上面的示例中，我们创建了一个名为mv_order_total的物化视图，使用了REFRESH FAST ON COMMIT选项指定了在提交时快速刷新物化视图，并且通过ENABLE QUERY REWRITE选项启用了查询重写功能。这个物化视图会根据订单明细表order_items中的数量和单价计算出每个订单的总金额，并将结果存储在物化视图中。

这样，我们就创建了一个基于主键的物化视图，用于在具体业务中统计每个订单的总金额。

2、基于 rowid 的物化视图案例

  假设有一个远程表 remote_table，其包含了销售订单信息，我们希望在本地数据库中创建一个基于 rowid 的物化视图，以提高查询效率。

创建一个基于 rowid 的物化视图，远程表可以有主键

下面是具体的创建代码：

1. 首先我们要创建一个名为 remote_table 的远程表，包含了销售订单信息，可以使用以下 SQL 语句来创建该表：

   CREATE TABLE remote_table (
       order_id NUMBER PRIMARY KEY,
       customer_id NUMBER,
       order_date DATE,
       total_amount NUMBER
   );
   

   在上述 SQL 语句中，我们创建了一个名为 remote_table 的表，包含了订单ID（order_id）、顾客ID（customer_id）、订单日期（order_date）和总金额（total_amount）等字段。订单ID被指定为主键，以确保每个订单的唯一性。

2. 创建物化视图日志
   在远程表 remote_table 上创建物化视图日志，记录表中的变化。

   CREATE MATERIALIZED VIEW LOG ON remote_table
   WITH ROWID, PRIMARY KEY (order_id)
   INCLUDING NEW VALUES;
   

3. 创建物化视图

   在本地数据库中创建物化视图，基于远程表 remote_table 的数据构建。

   CREATE MATERIALIZED VIEW sales_orders_mv
   REFRESH FAST ON DEMAND
   
   WITH ROWID
   AS
   SELECT * FROM remote_table;
   

   在以上语句中，REFRESH FAST ON DEMAND 表示使用快速刷新，并且需要手动触发刷新，WITH ROWID 表示使用 rowid 作为物化视图的主键。

4. 刷新物化视图

   手动触发物化视图的刷新，以保证物化视图中的数据与远程表中的数据保持一致。

   BEGIN
     DBMS_MVIEW.REFRESH('sales_orders_mv', 'F');
   END;
   

在这个案例中，我们创建了一个基于 rowid 的物化视图 sales_orders_mv，用于存储远程表 remote_table 中的销售订单信息。通过物化视图，我们可以提高对销售订单信息的查询效率，并且可以根据需要手动触发物化视图的刷新，以保证数据的一致性。