ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > 使用物化视图和查询重写功能

使用物化视图和查询重写功能

原创 数据库开发技术 作者：mrhaozi 时间：2009-12-14 16:34:01 0 删除 编辑

使用物化视图和查询重写功能

目的

在本教程中，您将了解如何利用强大的物化视图和查询重写功能。

所需时间

大约 2 个小时

主题

本教程包括下列主题：

概述
情景
前提条件
为销售历史模式实施模式更改
启用查询重写

分析物化视图更新和重写功能

Oracle 的查询重写功能
带有 GROUP BY 扩展项的增强的重写功能
使用重写或错误来控制语句的执行
分区与物化视图
使用带有 PMARKER 信息的物化视图
使用查询重写和部分陈旧的物化视图
使用 TUNE_MVIEW 使物化视图快速刷新
重置环境
总结

查看屏幕截图

将鼠标置于此图标上以加载和查看本教程的所有屏幕截 图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）

注意：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与 该步骤相关的屏幕截图。您可以单击单个屏幕截图将其隐藏。

概述

通过使用概要管理 (Summary Management) 特性，您可以减轻数据库管理员的工作负荷，这是因为您无需再手动创建概要，而且最终用户也不再必须知道已经定义的概要。一旦您创建了一个或多个物化视图 （它们和概要等效），最终用户就可以查询数据库中的表和视图。Oracle 服务器中的查询重写机制自动重写 SQL 查询以使用概要表。这一机制减少了从查询返回结果的响应时间。数据仓库内的物化视图对于最终用户或数据库应用程序来说是透明的。

Oracle 数据库为您提供用于更先进的重写和刷新机制的增强功能，以及全面的顾问与调整框架。这为您的环境带来了更加优化的物化视图，在占用额外空间最小的情况下提 升了性能。

注意：本教程不是物化视图的介绍。它假定读者对物化视图功能有基本了解。如果您需要更多关于某些 主题的背景信息，请参看 Oracle 数据仓库指南。

返回主题列表

案例

您将使用 SALES HISTORY (SH) 示例模式创建、修改并分析物化视图和重写功能。此研讨会依赖于在默认安装上对 SH 模式进行的一些小修改。

返回主题列表

前提条件

开始本教程之前，您应该：

1.

完成了教程在 Windows 上安装 Oracle 数据库 10g。
2.

下载 mv.zip 并将其解压缩到您的工作目录 (c:wkdir) 中。

返回主题列表

为销售历史模式实施模式更改

在您启动物化视图功能前，需要对现有的 Sales History 模式对象进行一些更改，还必须将一些额外的系统权限授予用户 SH。使用 SQL 文件 modifySH_10gR2.sql 来应用这些更改。

1.

启动一个 SQL*Plus 会话。选择开始 > 程序 > Oracle-OraDB10g_home > Application Development > SQL Plus。

（请注意：本教程假设您拥有 c:wkdir 文件夹。如果没有，则需要创建此文件夹，并将 mv.zip 的内容解压缩到此文件夹中。在执行脚本时，可指定路经。）

2.

以 SH 用户的身份登录。输入 SH 作为 User Name，并输入 SH 作为 Password。然后单击 OK。
3.

从 SQL*Plus 会话运行 modifySH_10gR2.sql 脚本。 

@c:wkdirmodifySH_10gR2.sql

获得的输出的底部应与以下图像匹配。


4.

执行 xrwutl.sql 脚本。 

@c:wkdirxrwutl.sql

获得的输出的底部应与以下图像匹配。

返回主题列表

启用查询重写

要启用查询重写，需要满足以下条件：

单个物化视图必须有 ENABLE QUERY REWRITE 子句。
必须将 QUERY_REWRITE_ENABLED 初始化参数设置为 TRUE（在 10g 中是默认设置）。或者，您可以将此参数设置为 FORCE，这将停用对重写计划的任何成本评估，并在任何可能的时候重写查询。
重写完整性模式和特定的物化视图状态必须匹配，以启用对这 一特定物化视图的重写。

在这一部分中，将启用查询重写。首先，需要确保有对数据库实例的基本初始化设置。为 此，请执行下列步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 set_rewrite_session.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdirset_rewrite_session

ALTER SESSION SET query_rewrite_integrity=TRUSTED;
ALTER SESSION SET query_rewrite_enabled=FORCE;
show parameters query

您将启用查询重写，并使用“trusted”模 式。这是最常用的完整性级别。在 trusted 模式中，优化程序相信物化视图中的数据是最新的，在维度中声明的关系和 RELY 约束条件是正确的。在这一方式中，优化程序还将使用预先建立的物化视图或基于视图的物化视图，并使用未执行和已经得到执行的关系。在这一模式中，优化程序 还“信任”已经声明但尚未 ENABLED VALIDATED 的主/唯一键约束条件和通过维度指定的数据关系。

有关 query_rewrite_integrity 的级别和 query_rewrite_enabled 的更多详细信息，请参考 Oracle 据仓库指南。


返回主题列表

分析物 化视图更新和重写功能

Oracle 数据库 10g 提供用于分析现有的和潜在的物化视图的过程。这使您能够充分利用所有强大的物化视图功能。

要分析其潜在物化视图的刷新和重写功能，请执行以下步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv1.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdircreate_mv1.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP  BY c.cust_id;

这个语句失败并引发下面的错误：

ORA-23413: table "SH"."CUSTOMERS" does not have 
           a materialized view log.

您可以尝试手动纠正这个错误并尝试再次创建此物化视图。但是，这是一个循环反复而且耗时的过程。而使用带有 dbms_mview package 的 Oracle 数据库 10g 功能，您可以轻松修复这个错误。您马上就会看到这一点。


2.

即便这个语句成功，您也不能了解其快速刷新功能的详细信息。通过使用 dbms_mview.explain_mview package，您对潜在物化视图的功能会有更深入的了解，从而能够在其 创建之前解决所有问题。

@c:wkdirexplain_mv1.sql

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

begin
   for crec in ( select capability_name, possible, 
                        related_text, msgtxt
                 from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
end;
/

在输出中，您会看到系统指向 SALES 表上的另外一个缺少的物化视图日志。建议始终在潜在物化视图创建之前，用上述 dbms_mview.explain_mview 包对其进行分析，而非对系统进行反复试验 (trial-and-error)。

除了 CUSTOMERS 和 SALES 上缺少的物化视图日志，系统还检测到您需要向此物化视图添加额外的聚合函数，来为所有类型的 DML 操作全面启用快速刷新功能。

系统建议的聚合函数是：

  • COUNT(*)
  • COUNT(amount_sold)

dbms_mview.explain_mview 包的输出在此显示。您会发现，它不仅包括物化视图的刷新功能，还包括物化视图的重写和分区变化跟踪（Partition-Change- Tracking，PCT）功能我们会在后面讨论这些功能。

3.

要纠正这一点，首先创建上面识别出的物化视图日志。

@c:wkdircreate_mv_logs1.sql

DROP MATERIALIZED VIEW LOG ON sales;

CREATE MATERIALIZED VIEW LOG ON sales
  WITH ROWID, SEQUENCE
  (prod_id, cust_id, time_id, channel_id, promo_id, 
   quantity_sold, amount_sold)
  INCLUDING NEW VALUES ;

DROP MATERIALIZED VIEW LOG ON customers;

CREATE MATERIALIZED VIEW LOG ON customers
  WITH ROWID, SEQUENCE
  (cust_id,cust_first_name,cust_last_name,cust_gender,cust_year_of_birth
  ,cust_marital_status,cust_street_address,cust_postal_code,cust_city
  ,cust_state_province,country_id,cust_main_phone_number,cust_income_level
  ,cust_credit_limit,cust_email)
  INCLUDING NEW VALUES;

这一个在后面会使用。

DROP MATERIALIZED VIEW LOG ON products;
CREATE MATERIALIZED VIEW LOG ON products
  WITH ROWID, SEQUENCE
  (prod_id,prod_name,prod_desc,prod_subcategory,prod_subcategory_desc
  ,prod_category,prod_category_desc,prod_weight_class,prod_unit_of_measure
  ,prod_pack_size,supplier_id,prod_status,prod_list_price,prod_min_price)
  INCLUDING NEW VALUES;
4.

再次检查潜在物化视图的功能。

@c:wkdirexplain_mv1a.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales, -
             COUNT(amount_sold) AS cnt_dollars, COUNT(*)  -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

BEGIN
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
END;
/

此潜在物化视图的快速刷新功能已经按期望进行了更改。

5.

现在创建物化视图。

@c:wkdircreate_mv1b.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales,
          COUNT(amount_sold) AS cnt_dollars,
          COUNT(*) AS cnt
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;
6.

explain_mview 过程也使用现有的物化视图。

@c:wkdirexplain_mv1b.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview('cust_sales_mv');

set serveroutput on

begin
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
     dbms_output.put_line(crec.capability_name ||': '||crec.possible);
     dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
    end loop;
end;
/


从用于物化视图的潜在 SQL 语句开始，您已经能够无须创建物化视图就能对其功能进行完全的分析了。

返回主题列表

Oracle 的查询重写功能

优化程序使用许多不同方法来重写查询。第一个，也是最重要的步骤，是确定查询所请求的全部或部分结果是否可以从 存储在物化视图的预计算结果中获得。

最简单的情况是，存储在物化视图中的结果与查询所请求的结果完全匹配。Oracle 优化程序通过将查询的文本与物化视图定义的文本相比较来作出这种类型的决定。这种方法是最简单的，但符合这种查询重写类型的查询的数量最少。

当文本比较测试失败后，Oracle 优化程序将基于联接、选择、分组、聚合以及抓取的列数据执行一系列通用检查。这是通过将查询的不同子句（如 SELECT、FROM、WHERE、HAVING 或 GROUP BY）与物化视图的不同子句分别进行比较而完成的。

使 用部分文本匹配重写
在 预先建立的表上创建物化视图
使 用简单的联回 (Join Back) 重写
分 析重写过程
使 用联回和卷积重写
使 用复杂的联回和卷积重写
在 数据的子集上创建物化视图
使 用多物化视图重写
估 计物化图的大小
分 析重写过程
用 联回和聚合卷积重写

返回主题列表

使用部分文本匹配重写

最简单的重写机制是文本匹配重写。在全文本匹配中，查询的整个文本会与物化视图定义的整个文本（即整个 SELECT 表达式）相比较 — 在文本比较过程中，忽略空白。在全文本匹配失败后，优化程序会尝试进行部分文本匹配。在这种方法中，以查询的 FROM 子句开始的文本会与以物化视图定义的 FROM 子句开始的文本进行比较。

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite1.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中： 

@c:wkdirexplain_rewrite1.sql

Rem REWRITE
DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132

set pagesize 999
select * from table(dbms_xplan.display);

该计划显示，使用部分文本匹配重写机制，通过 cust_sales_mv materialized 视图重写了查询。以 FROM 子句开始，SQL 语句和物化视图是相同的。

在重写查询的同时，以与访问普通表相同的方法对物化视图的访问计划进行了研究，以便可以使用所有现有索引。


2.

执行查询。

@c:wkdirdo_rewrite1.sql

set timing on

SELECT c.cust_id,
       SUM(amount_sold) AS dollar_sales
FROM   sales s, customers c
WHERE  s.cust_id= c.cust_id
GROUP BY c.cust_id;

这一查询的执行很快就有结果，因为它只访问在 cust_sales_mv 中已经联接和聚合的信息。
3.

可以使用 NOREWRITE 提示来实施针对非编写语句的 计划。这使对一个查询是否重写的控制能下至语句级别。

@c:wkdirexplain_norewrite.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            c.cust_id,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

如果没有查询重写功能，您必须从 SALES 和与 CUSTOMERS 的联接进行完整扫描。

注意：由于时间限制，不运行此查询。

返回主题

在预先构建的表 上创建物化视图：

在数据仓库环境中，有已经创建好的概要表或聚合表是很普遍的。您无须通过创建新的物化视图来重复此工作。这个解 决方案将改善物化视图的性能，但是，它不能：

为所有 SQL 应用程序提供透明查询重写
在一个应用程序中透明地访问在另一个应用程序中定义的物化视图
普遍支持快速并行或快速物化视图刷新

由于这些限制，而且由于现有的物化视图可能极为巨大且重建起来费用高昂，Oracle 数据库为您提供了将这些已经存在的概要表注册为物化视图的功能，从而避免了上述所有缺陷。您可以用 CREATE MATERIALIZED VIEW ...ON PREBUILT TABLE 语句注册物化视图。注册之后，物化视图可以用于查询重写，由刷新方法之一或二者一起维护。

Oracle 为其现有客户群实现了这一功能，以提供安全的移植路径并保护投资。移植带有“手工制造”的概要表和刷新过程的现有数据仓库环境，可以用单个 DDL 命令来利用重写功能，不会影响任何现有代码。

例如，MyCompany 最近从 Oracle9i 移植到了 Oracle10g，而且确实有这种手动创建的聚合表，在现有的所有数据仓库 系统中 90% 以上也是如此。要将现有的 cust_id_sales_aggr 表注册为物化视图，请执行以下步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv2.sql， 或将以 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdircreate_mv2.sql

DROP MATERIALIZED VIEW cust_sales_aggr ;

CREATE MATERIALIZED VIEW cust_sales_aggr
  ON PREBUILT TABLE
  REFRESH FORCE
  ENABLE QUERY REWRITE
  AS
    SELECT c.cust_id,
      c.cust_last_name,
      c.cust_first_name,
      SUM(amount_sold) AS dollar_sales,
      COUNT(amount_sold) AS cnt_dollars,
      COUNT(*) AS cnt
    FROM sales s, customers c
    WHERE s.cust_id= c.cust_id
    GROUP BY c.cust_id,
      c.cust_last_name,
      c.cust_first_name;

这个语句相当快。它完全不会触及任何数据。只是创建物化视图的元信息：哪些联接，哪些聚合，涉及到哪些表和列。

对于查询重写的最高级别的数据完整性 (query_rewrite_integrity=ENFORCED) 而言，在预先建立的表上使用物化视图是不可能的，因为对于数据完整性，系统“信任”您是创建者。只要您开始使用 Oracle 的刷新功能，系统就会知晓数据的完整性。但是，在任何情况下，第一次刷新都会是一次完整的刷新。

返回主题

使用简单的联回重写

当文本比较测试失败后，Oracle 优化程序将基于联接、选择、分组、聚合以及抓取的列数据执行一系列通用检查。这是通过将查询的不同子句（ SELECT、FROM、WHERE、HAVING 或 GROUP BY） 与物化视图的不同子句分别进行比较而完成的。要使查询重写发生，查询不必始终都完全匹配。例如，假定您的物化视图是按 cust_id 分组，而查询按 cust_last_name 分组。使用一种称为联回的方法，仍可以进行查询重写。

下面是联回重写的一个简单实例。cust_sales_mv 物化视图存储 cust_id 联接列，以与 sales 表和 customers 表确定 cust_credit_limit 值相同的方法与 customers 联接。

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite2.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdirexplain_rewrite2.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
  SELECT c.cust_last_name,
    c.cust_credit_limit,
    SUM(amount_sold) AS dollar_sales
  FROM sales s, customers c 
  WHERE s.cust_id= c.cust_id
  GROUP BY c.cust_last_name,
    c.cust_credit_limit
  ORDER BY 1;

set linesize 140
SELECT * FROM TABLE(dbms_xplan.display);

在此计划中，您可以看到，通过使用 cust_id 列（它是物化视图的组成部分，并表示 sales 表与 customers 表之间的主键-外键关系），Oracle 使用了 cust_sales_mv 物化视图并将其联接回 customers 表。此外，查询中所请求的属性 cust_last_name 是一个来自 cust_id 的确定属性，因而系统知道不必在维点上进行额外聚合。它只须将物化视图联回到 customers 表。关于层次结构和确定属性的信息是 customers_dim（用 于 customers 维的 Oracle 维对象）的组成部分。维定义 customers_dim 的重要部分显示如下： 

LEVEL customer IS (customers.cust_id)
…
ATTRIBUTE customer DETERMINES
(cust_first_name, cust_last_name, 
 cust_credit_limit, cust_gender,...

注意：关于维对象的更多信息，请参看 Oracle 数据仓库指南。
2.

执行查询。请注意，只计算结果，而且实际上并不输出 (spool out) 所有返回的记录。

@c:wkdirdo_rewrite2.sql

SELECT COUNT(*) 
FROM (SELECT c.cust_last_name, 
        c.cust_credit_limit,
        SUM(amount_sold) AS dollar_sales
      FROM sales s, customers c
      WHERE s.cust_id= c.cust_id
      GROUP BY c.cust_last_name,
        c.cust_credit_limit
       ORDER BY 1;
3.

用于非重写查询的计划可以通过以下语句显示：

@c:wkdirexplain_norewrite2.sql

DELETE FROM  plan_table;
COMMIT;

EXPLAIN PLAN FOR
  SELECT /*+ NOREWRITE */ c.cust_last_name,
    c.cust_credit_limit,
    SUM(amount_sold) AS dollar_sales
  FROM sales s, customers c
  WHERE s.cust_id= c.cust_id
  GROUP BY c.cust_last_name,
    c.cust_credit_limit
  ORDER BY 1;

set linesize 120
select * from table(dbms_xplan.display);

若不重新查询，则需要处理完整的 sales 事实表与 customers 维表之间的联接。

返 回主题

分析重写过程

若要了解系统中查询重写的可能候选对象的详细信息，可以使用 dbms_mview.explain_rewrite 过程。要分析之前执行的查询并更深入地了解重写过程，请执行以下步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 analyze_rewrite2.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中： 

@c:wkdiranalyze_rewrite2.sql

TRUNCATE TABLE rewrite_table;

DECLARE
  querytxt VARCHAR2(1500) := 'select c.cust_last_name, c.cust_credit_limit,'
    ||
    ' SUM(amount_sold) AS dollar_sales ' ||
    'FROM sales s, customers c ' ||
    'WHERE s.cust_id= c.cust_id ' ||
    'GROUP BY c.cust_last_name, c.cust_credit_limit';
BEGIN
  dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

SELECT message
FROM   rewrite_table
ORDER BY sequence DESC;

请注意，上面使用的物化视图并不是唯一适用的；基于预先建立的表的 cust_sales_aggr_id 物化视图也可进行重写了。在这种情况下，优化程序会基于成本做出决策。

返 回主题

使用联回和卷积重写

除了物化视图的简单联回（请求相同聚合级的信息），物化视图还可以聚合到一个更高的级别，即所谓的 ROLLUP（卷积）操作。请看以下查询：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite3.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdirexplain_rewrite3.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   c.cust_state_province,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

在此计划中，您可以看到，通过使用 cust_id 列（它是物化视图的组成部分，并表示 sales 表与 customers 表之间的主键-外键关系），Oracle 使用 cust_sales_mv 物化视图并将其联接回到 customers 表。

但是，可能的联接键列的存在并不一定代表了
查询重写机制保证数据完整性所需的全部信息。

检查 customers_dim 维定义：

LEVEL customer  IS (customers.cust_id)
LEVEL city      IS (customers.cust_city)
LEVEL state     IS (customers.cust_state_province)
. . .
HIERARCHY geog_rollup (
      customer     CHILD OF
      city         CHILD OF
      state        CHILD OF
. . .

所请求的 cust_state_province 属性表示州级，这是比用户级（由 cust_id 表示）更高的聚合级。级别和层次代表一种声明方法，用来表示一个表内的 1:n 关系。在这里，它表示在不影响数据完整性的情况下，将所有客户信息汇总到州级的有效性。每个不同的客户值都将获得一个唯一的州值。
2.

现在提交查询。其运行速度相当快。

@c:wkdirdo_rewrite3.sql

SELECT COUNT(*) 
FROM (SELECT   c.cust_state_province,
         SUM(amount_sold) AS dollar_sales
      FROM     sales s, customers c
      WHERE    s.cust_id= c.cust_id
      GROUP BY c.cust_state_province);

返 回主题

使用复杂的联回和卷积重写

下面的示例演示了查询重写功能的强大能力和灵活性。以下示例不但进行了联回，而且还使用 customers_dim 维中的息在雪花模式中的两个表上进行了联回。为此，执行下列步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 explain_rewrite4.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdirexplain_rewrite4.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   co.country_name, 
            c.cust_state_province, SUM(amount_sold) AS dollar_sales 
   FROM     sales s, customers c, countries co
   WHERE    s.cust_id= c.cust_id 
     AND    c.country_id = co.country_id 
   GROUP BY co.country_name, c.cust_state_province 
   ORDER BY 1,2;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

优化程序将重写查询以利用 cust_sales_mv 物化视图，将其联回到 customers 并将 customers 联接到 countries 以满足查询。利用与在不需重写的语句级别上强制执行查询相同的方法，可以强制使用特定的物化视图。

必须进行各种数据完整性检查，以保证使用此物化视图的有效性。除了检查丢失或非复制联接外，对维信息的评估在重写过程中也扮演着重要角色。

检查优化程使用什么来重写这个查询。以下是 customers_dim 的维定义摘录。它显示了这个查询的重要部分：

LEVEL customer    IS (customers.cust_id)
LEVEL . . .
LEVEL state       IS (customers.cust_state_province)
LEVEL country     IS (countries.country_id)
. . .
HIERARCHY geog_rollup (
       customer      CHILD OF
       . . .
       state         CHILD OF
       country       CHILD OF
. . .
JOIN KEY (customers.country_id) REFERENCES country
)
. . .
ATTRIBUTE country DETERMINES (countries.country_name)

Oracle 数据库必须确定，它是否可以基于存储在该物化视图中的信息导出所有被请求的属性。查询中正在请求 countries.country_name 和 customers.cust_state_province，而 该物化视图只包含 cust_id 信息。

根据 customers_dim 维中的信息，Oracle 数据库可以确定：

  • customers.cust_state_province 可以通过物化视图中的 cust_id
    确 定。它表示维中比客户级更高的聚合级。
  • countries.country_id 也可以通过物化视图中的 cust_id 确定。countries.country_id 描述比客户级更高的聚合级。
  • countries.country_name 是国家级层次的确定属性，
    因此可以基于 countries.country_id 来确定。
  • customers_dim 维描述了两个表之间的层次相关性。
    联接条件是维信息的组成部分。

Oracle 数据库通过所有这些信息，不但将物化视图与 customers 表联接，而且还将 customers 与 countries 联接，以获得查询的结果，并保证查询结果正确。
2.

重写的 SQL 语句如下所示：

@c:wkdirrewrite_sel.sql

SELECT COUNT(*) 
FROM  (SELECT co.country_name, 
         c.cust_state_province,
         SUM(mv.dollar_sales) AS dollar_sales
       FROM  cust_sales_mv mv, 
         (SELECT DISTINCT cust_id, cust_state_province, 
          country_id FROM customers) c, 
          countries co   
       WHERE mv.cust_id = c.cust_id
       AND   c.country_id = co.country_id
       GROUP BY co.country_name, c.cust_state_province
       ORDER BY 1,2);
3.

现在执行该查询。

@c:wkdirdo_rewrite4.sql

SELECT   c.cust_state_province,
         SUM(amount_sold) AS dollar_sales
FROM     sales s, customers c
WHERE    s.cust_id= c.cust_id
GROUP BY c.cust_state_province;
4.

如果希望无需查询重写即可获得此计划，可以运行 explain_norewrite4.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中。

@c:wkdirexplain_norewrite4.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */
          co.country_name,
          c.cust_state_province,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c, countries co
   WHERE  s.cust_id= c.cust_id
   AND    c.country_id = co.country_id
   GROUP BY co.country_name, c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

请注意，在这个计划中未出现物化视图重写。

返 回主题

在数据的子集上创建物化视图

很多时候，只是考虑对大型事实表中的信息子集进行更多的分析。以前，在这种情况下，为了利用物化视图，您需要为 事实表创建一个包含所有信息的物化视图。现在，您可以在物化视图中并入一个谓词条件，以允许 TEXTMATCH 只重写功能。

要在数据子集上创建一个物化视图，并将其创建间和大小与不带谓词的、包含相同联接、聚合以及完整数据集的物化视 图进行比较，请执行以下步骤：

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行 create_mv3.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdircreate_mv3.sql

DROP MATERIALIZED VIEW some_cust_sales_mv;

CREATE MATERIALIZED VIEW some_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE
   ENABLE QUERY REWRITE
   AS
   SELECT   c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
            sum(s.quantity_sold) as quantity
   FROM     sales s , customers c, products p

   WHERE    c.cust_id = s.cust_id
   AND      s.prod_id = p.prod_id
   AND      c.cust_state_province IN 
               ('Dublin','Galway','Hamburg','Istanbul')
   GROUP BY c.cust_id, p.prod_id;

记录下创建物化视图所耗费的时间。

2.

现在，创建相同的物化视图，只是其中无需谓词来限制结果集。

@c:wkdircreate_mv3b.sql

DROP MATERIALIZED VIEW all_cust_sales_mv;

CREATE MATERIALIZED VIEW all_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE  
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
          sum(s.quantity_sold) as quantity
   FROM   sales s , customers c, products p
   WHERE  c.cust_id = s.cust_id
   AND    s.prod_id = p.prod_id
   GROUP BY c.cust_id, p.prod_id;

这样创建物化视图用了更长的时间，这是由于所有数据都必须被触及、联接、聚合。

返 回主题

使用多物化视图重写

Oracle 数据库 10g 第 2 版中的新增内容是，为了解决查询，可使用多个物化视图进行查询重写。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16396910/viewspace-1029667/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle分布式系统数据复制技术
下一篇： oracle10g官方例子（很不错）
请登录后发表评论 登录
全部评论
mrhaozi

注册时间：2009-11-28

  • 博文量
    125
  • 访问量
    333468

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593

1.

创建以下两个物化视图，其中只包含 2001 年 11 月和 12 月的数据

在登录到 SH 模式的 SQL*Plus 会话中，运行 cr_qw_mmv.sql， 或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdircr_qw_mmv.sql
CREATE MATERIALIZED VIEW nov_2001 
BUILD IMMEDIATE REFRESH COMPLETE 
ENABLE QUERY REWRITE AS 
SELECT cust_id, prod_id, time_id, sum(amount_sold) AS dollars 
FROM sales s 
WHERE time_id >= TO_DATE('01-NOV-2001','DD-MON-YYYY') AND 
time_id GROUP BY time_id, cust_id, prod_id; 

CREATE MATERIALIZED VIEW dec_2001 
BUILD IMMEDIATE REFRESH COMPLETE 
ENABLE QUERY REWRITE AS 
SELECT cust_id, prod_id, 
time_id, sum(amount_sold) AS dollars 
FROM sales s 
WHERE time_id >= TO_DATE('01-DEC-2001','DD-MON-YYYY') AND 
time_id GROUP BY time_id, cust_id, prod_id;

2.

现在执行一个查询，获取在 11 月 5 日至 12 月 15 日期间按客户分类的销售额。运行 explain_mmv.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：

@c:wkdirexplain_mmv.sql
DELETE FROM plan_table; 
COMMIT; 
EXPLAIN PLAN FOR 
SELECT cust_id, sum(amount_sold) AS dollars 
FROM sales s 
WHERE time_id >= TO_DATE('05-NOV-2001','DD-MON-YYYY') AND 
time_id GROUP BY cust_id;

--See how the query uses only these two materialized views to obtain the data 

set linesize 132 
set pagesize 999 

select * from table(dbms_xplan.display);