WEB开发网
开发学院数据库DB2 利用物化查询表提高 WebSphere Information Integ... 阅读

利用物化查询表提高 WebSphere Information Integrator 的性能

　2009-12-24 00:00:00　来源：WEB开发网　　　
核心提示：简介物化查询表 (MQT) 是一个这样的表，它物化了一个涉及一个或多个表或昵称的查询的预先计算的结果，利用物化查询表提高 WebSphere Information Integrator 的性能，当创建并填充好 MQT 之后，一个任意的后续查询就会由 MQT 来满足，并使得使用 MQT 的过程对用户是透明的，我们确信，

简介

物化查询表 (MQT) 是一个这样的表，它物化了一个涉及一个或多个表或昵称的查询的预先计算的结果。当创建并填充好 MQT 之后，一个任意的后续查询就会由 MQT 来满足，条件是 MQT 匹配该查询的所有部分或一部分。要了解更多信息，请参见 [1]。大家都知道，使用 MQT 高速缓存数据和允许查询优化器作出一个基于成本的决策以使用 MQT 来满足查询可以改善 Database Management System (DBMS) 中的查询性能。在诸如 WebSphere Information Integrator 之类的联邦系统中使用 MQT 出于以下原因可以更加有益：

联邦查询一般在远程系统上执行部分（或整个）查询，并通过网络将即时结果返回给联邦系统。由于网络的延迟，以联邦模式运行查询一般比只访问本地数据的相同查询要慢一些。涉及昵称的 MQT 使得远程数据本地可用，因此省去了通过网络到远程数据源的来回。

如果需要从中获得查询数据的一个远程 DBMS 不可用，那么在查询的访问计划决定使用 MQT 而非远程数据来满足查询时，查询将仍然能够产生结果。

本文介绍 WebSphere Information Integrator 中的 MQT。展示如何定义适合于工作负载的 MQT，以及如何填充它们。给出在 WebSphere Information Integrator 中使用 MQT 的性能结果。给出用于确定查询是否使用了 MQT 的提示和技巧，如果未使用，是什么防止了 MQT 的使用。还例举了使用 MQT 的限制。

概述

本文首先在 理解 MQT 一节给出一个启发性例子，展示 MQT 如何可以改善查询性能。然后简要解释了 如何作出在查询中使用 MQT 的决策 和 在联邦环境中使用 MQT 的优势。创建和使用 MQT 的步骤 讨论如何选择可能改善工作负载的性能的 MQT。这一节详细介绍了创建一个 MQT、用数据填充它以及为了优化而启用它的所有步骤。关于 故障诊断 的一节帮助您确定为什么没有使用 MQT 来满足查询。这一节还列出了关于使用 MQT 的 限制。本文的最后一节描述我们的内部 性能试验 和使用 MQT 获得的性能优势。

理解 MQT

本节介绍一个示例业务场景，并逐步介绍使用 MQT 的需要，以及创建和启用它的步骤。

一个启发性例子

考虑一家公司的总部在纽约，而数据仓库在圣何塞。数据仓库跟踪该公司在美国销售的所有产品的销售记录。销售数据维护在具有以下模式的表 'sales' 中。

CityStateProduct_nameQuantityPriceTransaction_date

sales 表中的一行是给定日期、城市和州销售的特定产品的总数量和总价格。位于纽约的总部采用一个 WebSphere Information Integrator 系统。WebSphere Information Integrator 系统相应于圣何塞数据库中的 'sales' 表具有一个昵称 'divisional_sales'。

考虑总部的销售经理想要查看加利福尼亚州在 2005 年销售的产品。他们需要的是按城市聚集的信息。

按城市计算总销售的查询可以写成：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY;　

经理们会经常运行这类查询，有时会在 City 或 SUM_SALES 列添加更多的过滤条件。经理们希望找到的上一个查询的一个变体是查看在城市圣何塞中销售的产品。

这一要求可以表达为在上一个查询中添加一个谓词 CITY = 'SAN FRANCISCO'。该查询类似于：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　CITY='SAN　FRANCISCO'　
AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005';　

当经理们想要找到总销售量小于 $100,000 的城市时，会产生另一个变体。该信息可以表达为在上面的查询中添加一个谓词 SUM_SALES < 100000，如下所示：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY　
HAVING　SUM(PRICE)　<　100000;　

销售信息经常更新到 sales 表中，但这不是说经理们必须得到最新的信息。查询的业务环境能够忍受已经过期几个小时甚至几天的信息。在本例中，得出总销售信息是一个计算复杂的任务。而且，计算会对一些稍微不同的条件重复进行多次。一个需要指出的重要特征是，稍微过期的数据是可以接受的。可以定义一个 MQT，使得部分计算可以只执行一次并存储结果，以便后续的查询只需要很少的额外处理就可以从高速缓存的计算结果获得答案。

满足以上三个查询变体的一个可能的 MQT 定义类似于：

CREATE　TABLE　AGGREGATE_SALES　(CITY,　TOTAL_SALES)　AS　
(SELECT　CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY)　
DATA　INITIALLY　DEFERRED　REFRESH　DEFERRED　
ENABLE　QUERY　OPTIMIZATION　
MAINTAINED　BY　SYSTEM;　

MQT 定义中的子句指出 MQT 的行为。稍后的几节中将详细讨论这些子句。一旦 MQT 已经创建并填充好，当用户提交一个查询以找到总销售数据或变体信息时，就会通过使用 aggregate_sales MQT 来满足该查询。

如何作出在查询中使用 MQT 的决策

为了使用 MQT，用户不必要更改查询。一旦 MQT 已经创建并用数据填充好了，WebSphere Information Integrator 优化器就可以确定用户查询是否可以通过使用 MQT 来满足，以及使用 MQT 是否会带来性能优势。为了优化器使用 MQT 来取代部分或整个查询，需要满足以下条件：

MQT 应用用户查询考虑应用的谓词的相同集合或子集。即使 MQT 只包含用户查询应用的谓词的一个子集，它仍然是可以使用的，因为优化器通过向从 MQT 返回的数据添加缺少的谓词进行了弥补。

MQT 选择用户查询考虑选择的列的相同集合或超集。如果查询比相应匹配的 MQT 选择较少的列，那么优化器可以消除不必要的列。

其他条件与 MQT 中数据的新鲜性和用于运行查询满足的优化级别有关。“如何使用和创建 MQT” 一节中将介绍这些条件。

在联邦环境中使用 MQT 的优势

出于各种原因，MQT 是有益的。下面描述了其中一些原因：

计算昂贵的查询的性能可以得到改善。缓存昂贵计算的结果一次，并多次使用缓存的结果，可以带来性能优势。它也使用较少的 CPU，并防止系统过载。

对多个查询公共的计算可以使用 MQT 来表示。MQT 使用数据填充一次，被多个查询重用，从而带来性能优势。

MQT 将数据本地缓存在联邦系统中，可以避免从后端数据源到传输数据的昂贵循环。

如果向查询提供数据的后端数据源断开了，并且优化器选择使用 MQT 来满足查询，那么查询将会执行。因此，数据源的不可用性将不会妨碍查询的执行。

MQT 向优化器提供重要的统计信息。更具体地说，收集了其最近的统计信息的 MQT 的存在可以帮助优化器确定谓词的过滤级别，例如，以更加精确的方式计算谓词的选择。

创建和使用 MQT 的步骤

步骤 1：使用 Design Advisor 来确定将会有助于改善查询工作负载性能的 MQT 集合

将会有助于改善工作负载性能的 MQT 的种类可由对查询的分析或通过使用诸如 Design Advisor 之类的工具来确定。本节将讨论确定 MQT 可能对工作负载是有益的。

DB2® Design Advisor 帮助用户创建物化查询表 (MQT) 和索引、重新分配表并转换到多维集群 (MDC) 表，以及删除未用的对象。所有的推荐基于用户提供的一个或多个 SQL 语句。一组相关的 SQL 语句也叫做一个工作负载。用户可以为一个工作负载中的每个语句指定重要级别，并指定工作负载中每个语句执行的频率。Design Advisor 创建一个 SQL 脚本输出，其中包含用于创建推荐的对象的 CREATE INDEX、CREATE SUMMARY TABLE (MQT) 和 CREATE TABLE 语句。

在本文中，我们着重讲述如何使用 Design Advisor 来为联邦查询推荐 MQT。Design Advisor 是通过 DB2 Control Center 或 "db2advis" 命令调用的。

下面这个例子演示如何使用 Design Advisor 来为联邦工作负载推荐 MQT。在这个例子中：

组成工作负载的查询从文本文件 'my_queries.sql' 读取，

工作负载应用于数据库 'mydb' 中定义的对象上，且

MQT DDL 语句保存在文件 'my_rec_mqts.sql' 中。

Advisor 将推荐 REFRESH DEFFERED MQT。

db2advis　-d　mydb　-i　my_queries.sql　-m　M　-o　my_rec_mqts.sql　-u　-k　OFF

Design Advisor 生成的输出文件包含一组 DDL 语句，用于创建 MQT、刷新和更新 MQT 上的统计信息，以及在其上创建索引。输出文件一生成就可以使用，或者为应用程序的特定需求进行定制。通过从 MQT 删除过滤器（本地）谓词，Design Advisor 为给定查询一般化推荐的 MQT。例如，如果 Design Advisor 是在这样一个查询上调用，该查询从三个表选择数据，并且包含三个联结谓词和两个过滤器谓词，那么推荐的 MQT 将不会包含任何过滤器谓词。如果您认为自己的查询总是使用相同的常量值过滤数据，那么您可能选择在 MQT 中包含一些过滤器谓词。

步骤 2：创建 MQT

一旦确定了 MQT 定义，就可以使用 "CREATE TABLE" 语句创建 MQT 了。我们将使用前面 “理解 MQT” 一节中的例子。我们的示例 MQT 类似于：

CREATE　TABLE　AGGREGATE_SALES　(CITY,　TOTAL_SALES)　AS　
(SELECT　CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY)　
DATA　INITIALLY　DEFERRED　REFRESH　DEFERRED　
ENABLE　QUERY　OPTIMIZATION　
MAINTAINED　BY　SYSTEM;　

创建 MQT 时需要仔细地确定要使用的子句。

'DATA INITIALLY DEFERRED' 子句指出，当创建 MQT 时，不会作为 create table 语句的一部分填充 MQT。

'REFRESH DEFERRED' 子句指出，使用 'REFRESH' 语句将用数据填充 MQT。对 MQT 定义中使用的表或昵称进行的数据插入、更新或删除等形式的任何更改将不会自动导致 MQT 中数据的插入、更新或删除。'REFRESH DEFERRED' 的一个替代是 'REFRESH IMMEDIATE' 选项。该选项指定，对 MQT 定义中使用的表进行的数据插入、更新或删除等形式的任何更改将会自动导致 MQT 中数据的更改。该选项对其定义引用昵称的 MQT 不可用。在本文中，我们将通过昵称使用 MQT，因此这些 MQT 将总是定义为 'REFRESH DEFERRED' MQT。

MQT 可以通过设置 'ENABLE QUERY OPTIMIZATION' 子句而对优化器可用。'DISABLE QUERY OPTIMIZATION' 子句可以用来防止优化器考虑使用特定的 MQT。

'REPLICATED' 子句指出，在启用 Data Partitioning Feature (DPF) 的系统的所有分区上建立 MQT 的一个副本。在这样的系统中，有可能指定 MQT 的分区或者将它定义为 REPLICATED MQT。如果 MQT 被定义为 REPLICATED，那么可以使用该 MQT 的操作（比如，联结）就可能获得更好的性能，并通过排列（collocation）将它与相同节点上的数据相关联。

MQT 可以根据以下用数据填充它们时所使用的机制来分类：

System-Maintained MQTs：系统维护的 MQT 中的数据由 WebSphere Information Integrator 系统来管理。'REFRESH TABLE' 语句可用于用数据填充 MQT。不允许用户直接在 MQT 中插入、更新或删除数据。

User-Maintained MQTs：顾名思义，用户维护的 MQT 由用户维护。'REFRESH TABLE' 语句不适用于用户维护的 MQT。用户可以在用户维护的 MQT 中插入、更新或删除数据。用户也可以使用 'LOAD' 实用工具来用数据填充 MQT。由用户来负责确保用户维护的 MQT 包含与 MQT 的定义一致的数据。

Federated_Tool- Maintained MQTs 或 Cache Tables：定义为 'MAINTAINED BY FEDERATED_TOOL' 的 MQT 由自动复制维护。这些 MQT 不支持使用 'REFRESH TABLE' 语句。Federated_tool 维护的 MQT 也叫做 Cache Tables。它们是用户维护的 MQT 的特殊子类型，其中的数据是通过使用 Replication 填充的。"Cache tables" 只可使用 Control Center 来设置。有一些限制只应用于 Cache tables，不应用于系统维护的和用户维护的 MQT。这些限制将在下面的 限制 和 故障诊断 小节中详细介绍。

步骤 3：使用 SET INTEGRITY 语句使 MQT 脱离 check-pending 状态

该步骤只应用于用户维护的 MQT，并且启用 MQT 的数据填充。在我们的例子中，'aggregate_sales' 被定义为用户维护的 MQT，必须发出下面这个语句使 MQT 脱离 check-pending 状态。

SET　INTEGRITY　FOR　AGGREGATE_SALES　ALL　IMMEDIATE　UNCHECKED;　


"SET INTEGRITY" 语句指定，MQT 将打开完整性检查，而不检查完整性违反。

该步骤对于系统维护的 MQT 的不是必需的，因为用于填充数据的 REFRESH 语句使系统维护的 MQT 脱离 check-pending 状态。

步骤 4：用数据填充 MQT

一旦创建了 MQT，就需要用数据填充它，以反映 MQT 将缓存的查询结果。每当 MQT 的内容基于底层数据被更新时，也需要执行该步骤。

用数据填充系统维护的 MQT：

如果 MQT 是系统维护的 MQT，那么可以使用 'REFRESH TABLE' 语句用数据填充它。可以针对我们的示例 MQT 调用 "REFRESH TABLE" 语句，如下所示：



REFRESH　TABLE　AGGREGATE_SALES;　

每当针对一个其中定义了昵称的 MQT 调用 REFRESH 语句时，该语句会删除 MQT 中预先存在的任何数据并重新用数据进行填充。增量刷新不可用。根据查询的复杂性和结果集的大小，刷新操作可能需要很长时间。您可以在任务中心设置任务，使 MQT 中诸如刷新之类的操作在您选择的时间发生。

用数据填充用户维护的 MQT：

如果 MQT 是用户维护的，那么可以使用 INSERT 或 LOAD 语句来填充它。如果我们的示例 MQT 是用户维护的 MQT，那么可以使用 INSERT 语句来填充它，如下所示：



INSERT　INTO　AGGREGATE_SALES　(SELECT　CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY);　

用户维护的 MQT 也可以使用 LOAD 语句来填充。下面列出了完成该操作的步骤：

使用 'DECLARE CURSOR' 语句在查询的评估上定义一个游标。查询的结果将在下一个 LOAD 语句中被插入 MQT 中。



DECLARE　CUR1　CURSOR　FOR　
SELECT　CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY;　

使用 “Load 命令” 将数据加载到 MQT 中。



LOAD　FROM　CUR1　OF　CURSOR　INSERT　INTO　AGGREGATE_SALES;　

LOAD 命令的 'INSERT' 选项将数据插入 AGGREGATE_SALES 表中。这可能会导致重复的值。LOAD 的 'REPLACE' 选项可用来首先删除 AGGREGATE_SALES 表中现有的数据，然后再插入新的数据。

我们的试验表明，当需要将相对较大的数据加载到 MQT 中时，LOAD 执行得比 INSERT 更快。您可能想要试验这两个选项，以确定 LOAD 和 INSERT 哪一个对您的场景来说性能更好一些。

您可以考虑在任务中心设置一个任务，使用户维护的 MQT 中的数据更新发生在您选择的时间。

用数据填充 federated_tool 维护的 MQT

一个复制计划被自动设置在 MQT 和该 MQT 所基于的昵称之间。复制使得数据保存在当前 MQT 中。

步骤 5：在 MQT 上创建索引

一旦创建了 MQT，您可能就想检查是否应该在 MQT 上创建任何索引。这可通过 "CREATE INDEX" 语句来执行。因为 MQT 是缓存查询结果的本地表，所以在本地表上创建索引的相同步骤也可应用于在 MQT 上创建索引。如果 MQT 具有一个键，那么应该创建惟一的索引以反映这一事实。索引的创建，惟一或不惟一，都有助于以不同的方式从 MQT 读取数据，导致更好的性能。

注意，惟一的索引不能创建在系统维护的 MQT 上。对于我们的例子来说，如果 'City' 是 MQT aggregate_sales 的键，那么 aggregate_sales 是用户维护的 MQT，可在该 MQT 上创建惟一的键，如下所示：

CREATE　UNIQUE　INDEX　uniq_city_index　ON　aggregate_sales　(City);　

步骤 6：在昵称上定义信息约束

在昵称上定义 “信息约束” 以反映底层表上的约束，有助于甚至在缺少 MQT 的情况下优化性能。当存在 MQT 时，信息约束有时有助于优化器向 MQT 匹配一个查询，这在缺少信息约束时 MQT 是不会考虑匹配的。我们用一个例子来演示这一点：

假设除了前面描述的 DIVISIONAL_SALES 昵称以外，用户还拥有昵称 'OFFICE_INFO'。'OFFICE_INFO' 昵称具有 CITY、OFFICE_ADDR 和 PHONE_NUM 等列。

OFFICE_INFO 昵称中的列 'CITY' 是惟一的，因此在列 CITY 上定义了昵称 OFFICE_INFO 的惟一约束，如下所示：

ALTER　NICKNAME　OFFICE_INFO　ADD　CONSTRAINT　UNIQ_CONS　UNIQUE　(CITY)　
NOT　ENFORCED　ENABLE　QUERY　OPTIMIZATION;　

昵称 DIVISIONAL_SALES 中的列 CITY 总是在昵称 OFFICE_INFO 中找到一个匹配的城市，因此在昵称 OFFICE_INFO 和 DIVISIONAL_SALES 之间的列 CITY 上定义了一个信息参考完整性约束，如下所示：

ALTER　NICKNAME　DIVISIONAL_SALES　ADD　CONSTRAINT　CITY_CONSTRAINT　
FOREIGN　KEY　(CITY)　REFERENCES　OFFICE_INFO(CITY)　NOT　ENFORCED　
ENABLE　QUERY　OPTIMIZATION;　

还与前面描述的一样，假设 MQT aggregate_sales 已经存在。

现在，用户发出一个查询：

SELECT　DS.CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　DS,　OFFICE_INFO　OI　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
AND　DS.CITY　=　OI.CITY　
GROUP　BY　DS.CITY;　

因为参考完整性约束确保 DIVISIONAL SALES 中 CITY 的每个值在 OFFICE_INFO 中找到一个匹配，所以优化器可以安全地消除到 OFFICE_INFO 昵称的联结和到 MQT AGGREGATE_SALES 的路由。

步骤 7：使用 runstats 收集 MQT 上的统计信息

一旦创建了 MQT 和相关的索引，就最好在 MQT 上运行 "runstats" 以收集统计信息。WebSphere Information Integrator 优化器是基于成本的，并依赖于所有对象（包括 MQT）的精确统计信息来作出执行计划决策。对于我们的示例 MQT，runstats 可以运行来收集统计信息，如下所示：

RUNSTATS　ON　TABLE　sample.aggregate_sales　WITH　DISTRIBUTION　AND　DETAILED　INDEXES　ALL;　

这里的 'sample' 是模式，MQT aggregate_sales 是在该模式下创建的。

步骤 8：允许为优化考虑 MQT

有两种方式允许为优化考虑 MQT。一个 MQT 也叫做一个维护表（maintained table）。

使用 'CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION' 特殊注册表。特殊注册表可以设置为一个希望的值，以便为会话启用特定的维护表对象。"SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement" 识别可在动态 SQL 查询优化过程中考虑的 MQT 的类型。可能的值有：

SYSTEM：只为优化考虑系统维护的 MQT。这是默认值。

ALL：将为优化考虑所有维护表类型。

NONE：不会为优化考虑任何维护表类型。

FEDERATED_TOOL：将只为优化考虑定义为 'MAINTAINED BY FEDERATED_TOOL' 的维护表。

USER：将只为优化考虑定义为 'MAINTAINED BY USER' 的维护表。

例如，为了指示优化器应该为优化考虑所有维护表类型，您应该像下面这样设置特殊注册表：



SET　CURRENT　MAINTAINED　TABLE　TYPES　FOR　OPTIMIZATION　ALL;　

使用数据库配置参数 'DFT_MTTB_TYPES'。这个 "dft_mttb_types - Default maintained table types for optimization configuration parameter" 为 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 特殊注册表指定默认值。注册表的值又确定在查询优化期间将使用何种类型的刷新延迟物化查询表。这一数据库配置参数的有效值有 ALL、NONE、FEDERATED_TOOL、SYSTEM 和 USER，语义与上面为特殊注册表的值描述的语义相同。该数据库配置参数的默认值是 'SYSTEM'。

例如，为了指示只为优化考虑定义为 'MAINTAINED BY USER' 的维护表，您应该像下面这样为数据库 SAMPLE 设置配置参数：

DB2　UPDATE　DATABASE　CONFIGURATION　FOR　SAMPLE　USING　DFT_MTTB_TYPES　USER;　

步骤 9：设置 REFRESH AGE 特殊注册表

REFRESH AGE 指定 MQT 中的过期数据是可忍受的。优化器将考虑只在 REFRESH AGE 被设置为非零值且优化级别被设置为 2 或 5 或更高时才使用 MQT 来处理查询。REFRESH AGE 只应用于 'REFRESH DEFERRED' 类型的 MQT。当前支持两个 REFRESH AGE 值。

0：这表明 'REFRESH DEFERRED' MQT 将不被优化器考虑来处理查询。

关键字 'ANY'：该设置表明 MQT 将被优化器考虑来处理查询。

REFRESH AGE 可以两种方式之一进行设置：

“设置 CURRENT REFRESH AGE 特殊注册表”

将 CURRENT REFRESH AGE 特殊注册表设置为值 ANY 使得在当前连接上下文中，将为优化考虑所有刷新延迟的 MQT。

“设置 DFT_REFRESH_AGE 数据库配置参数”

REFRESH_AGE 的默认值可以设置为数据库配置参数。例如：



DB2　UPDATE　DATABASE　CONFIGURATION　FOR　SAMPLE　USING　DFT_REFRESH_AGE　0;　

该设置防止为优化考虑刷新延迟的 MQT。如果设置了特殊注册表的值，特殊注册表将优于数据库配置参数。如果没有将特殊注册表设置为 any 值，那么数据库配置参数的值将确定 REFRESH AGE。

两种参数都以默认值 0 开始，例如，默认情况下不为优化考虑 'REFRESH DEFERRED' MQT。

步骤 10：找出是否使用 MQT 来回答查询

您可以使用 Visual Explain、Dynamic Explain 或 db2exfmt 工具的输出来找出是否使用 MQT 来满足查询。在本节中，我们将展示 db2exfmt 输出的一个片段来解释如何确定是否使用了 MQT。

当访问计划使用了 MQT 时，db2exfmt 输出将展示一个被访问的 MQT 对象。此外，对于某些查询，db2exfmt 输出中优化的 SQL（这是查询重写的输出）展示了 MQT 的使用而非基本昵称。在 db2exfmt 输出中，优化的 SQL 紧接在最初的 SQL 语句后面。

对于下面整个查询：

SELECT　CITY,　SUM_SALES　
FROM　
(SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY)　AS　TOTAL_SALES　
WHERE　SUM_SALES　<　100000;　

注意，该查询被重写来访问 aggregate_sales MQT。

SELECT　Q1.CITY　AS　"CITY",　Q1.TOTAL_SALES　AS　"SUM_SALES"　
FROM　SAMPLE.AGGREGATE_SALES　AS　Q1　
WHERE　(Q1.TOTAL_SALES　<　100000);　

计划片段类似于：

　　　Rows　
　　　RETURN　
　　　(　　1)　
　　　Cost　
　　　　I/O　
　　　　|　
　　　20.6667　
　　　TBSCAN　
　　　(　　2)　
　　　20.1968　
　　　　1　
　　　　|　
　　　　62　
　TABLE:　SAMPLE　　
　AGGREGATE_SALES　

该计划展示了 aggregate_sales MQT 上的一个表扫描。此外，操作符表扫描的详细信息展示了以下信息：

　　　　　　　Input　Streams:　
　-------------------　
　　1)　From　Object　SAMPLE.AGGREGATE_SALES　
　
　　Estimated　number　of　rows:　62　
　　Number　of　columns:　　3　
　　Subquery　predicate　ID:　　Not　Applicable　
　
　　Column　Names:　
　　------------　
　　+Q1.$RID$+Q1.TOTAL_SALES+Q1.CITY　

访问计划中使用的对象的列表显示在 db2exfmt 输出的末尾。对于我们的例子来说，显示了以下信息。注意，昵称 divisional_sales 显示为访问计划中使用的对象中的一个。但是单词 '(reference only)' 表明昵称引用在查询中，但是不被访问来满足查询。

Objects　Used　in　Access　Plan:　
---------------------------------------　
　
　Schema:　SAMPLE　　
　Name:　DIVISIONAL_SALES　
　Type:　Nickname　(reference　only)　
　
　Schema:　SAMPLE　　
　Name:　AGGREGATE_SALES　
　Type:　Table　
　　Time　of　creation:　　2006-02-17-17.16.21.751126　
　　Last　statistics　update:　
　　Number　of　columns:　　2　
　　Number　of　rows:　　62　
　　Width　of　rows:　　44　
　　Number　of　buffer　pool　pages:　1　
　　Distinct　row　values:　　No　
　　Tablespace　name:　　USERSPACE1　　　　　
　　Tablespace　overhead:　　12.670000　
　　Tablespace　transfer　rate:　0.180000　
　　Source　for　statistics:　　Single　Node　
　　Prefetch　page　count:　　32　
　　Container　extent　page　count:　32　
　　Table　overflow　record　count:　0　
　　Table　Active　Blocks:　　-1　

MQT 优于昵称的限制

存在一些 MQT 优于昵称的限制，以便联邦查询使用 MQT。这些限制可分为三类：

分区数据库环境中的 MQT 优于昵称限制

MQT 优于非关系昵称限制

Design Advisor 建议的 MQT 优于昵称限制

分区数据库环境中的 MQT 优于昵称限制

WebSphere Information Integrator 不支持在分区数据库环境中引用昵称的系统管理的物化查询表。请参考 “分区数据库环境中的 MQT 优于昵称限制”，了解关于如何处理该限制的提示。

MQT 优于非关系昵称限制

存在一组特定于每个非关系数据源的 MQT 优于非关系昵称限制。这些限制由必需的谓词所引入，并隐含了特定数据源的昵称之间的关系。例如，Blast 和 HMMER 数据源的昵称使用一些谓词来将值组合到充当传递给 Blast 和 HMMER 工具的输入参数的列。因此，定义在这类昵称上的 MQT 必须指定这些谓词；否则，MQT 上的刷新将会失败，或者使用 MQT 的查询将产生不正确的结果。另一个例子是 XML 昵称的限制，即不能为子 XML 昵称创建 MQT。MQT 必须从根昵称到这个子昵称引用昵称的整个层次结构。

Design Advisor 推荐的 MQT 限制

Design Advisor 生成的 MQT 有很少的几个限制和考虑因素，如下所示。

Design Advisor 不推荐用户维护的 MQT。因为昵称上的系统维护的 MQT 在分区数据库配置中是不受支持的，所以您不能在这样的环境中使用 Design Advisor。

当 Design Advisor 用于为联邦查询推荐 MQT 时，选项 -r 没有效果。选项 -r 指示 Design Advisor 为考虑的 MQT 使用扩展的统计信息。扩展的统计信息是使用采样在 MQT 定义中引用的对象上收集得到的。因为不支持在昵称上采样，所以 Design Advisor 使用优化器评估的统计信息。

为 MQT 推荐的索引被设计来改善工作负载性能而非 MQT 刷新性能。

如果更新、插入或删除未包含在指定的工作负载中，则不考虑更

新推荐的 REFRESH IMMEDIATE MQT 的性能影响。

故障诊断

关于查询为什么不使用 MQT 的提示和技巧

如果您的查询未使用 MQT，而您认为它应该使用，那么请检查：

确保 CURRENT REFRESH AGE 特殊注册表被设置为 ANY。这个特殊注册表是在发起到数据库的连接时用 DFT_REFRESH_AGE 数据库配置参数的值初始化的。也可以使用命令 SET CURRENT REFRESH AGE ANY 来显式设置它。甚至在刷新年龄是 0 时，也会为优化考虑缓存表（Control Center 工具创建的 MQT）。

确保为优化启用了 MQT。如果在 MQT 的 CREATE TABLE 或 ALTER TABLE 语句期间指定了选项 ENABLE QUERY OPTIMIZATION，那么在优化期间会考虑 MQT。

对于刷新延迟的 MQT，确保特殊注册表 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 被设置为一个包含您的 MQT 种类的值。例如，如果考虑的 MQT 是一个刷新延迟的系统维护的 MQT，那么这个特殊注册表必须设置为 ALL 或 SYSTEM。这个特殊注册表是用 DFT_MTTB_TYPES 数据库配置参数的值初始化的。

确保 MQT 中引用的表是查询中引用的表的一个子集。在有些情况下，WebSphere Information Integrator 可能选择一个从比查询更多的表选择的 MQT。例如，当 MQT 中的两个或多个表定义了引用完整性关系并且联结谓词在它们自己的主键 - 外键列上时，就会出现这种情况。

确保 MQT 应用的谓词是查询应用的谓词的一个子集。

确保查询是一个动态编译的语句。不为静态编译的查询考虑 MQT。

作为一个好的实践，确保 runstats 已经运行在 MQT 及其索引上以更新统计信息，因为 WebSphere Information Integrator 使用一个基于成本的优化器为查询生成访问计划。

如果您创建了许多 MQT（所有这些 MQT 都可以用于满足查询），那么查询的编译时间会增加。许多备选 MQT 的存在可能会防止编译器为该查询选择最好的 MQT。

如果查询是在比创建 MQT 时更高的隔离级别执行，那么将不会为查询的执行考虑该 MQT。

使用 MQT 时应该记住的一些事情：

如果查询在谓词或选择列表中具有一个函数模板，那么该函数模板必须是物化查询表的一部分。MQT 中不包含函数模板会导致在运行查询并选择 MQT 来满足查询时出现 "statement not supported" 错误。

使用 INSERT 语句填充并更新了用户维护的 MQT 后，通过检查解释输出确保相同的 MQT 不被用作插入操作的源。将 REFRESH AGE 设置为 0 或者将特殊注册表 'SET CURRENT MAINTAINED TABLE TYPE FOR OPTIMIZATION' 更改为一个不包含用户维护的 MQT 的值，将确保 MQT 本身不会用作 INSERT 语句的源。

缓存表类似于具有以下限制的 MQT，即缓存表只支持单个表上的非聚集查询。当 WebSphere Information Integrator 和远程数据源之间的复制不被支持时，用户维护的 MQT 应该用于取代缓存表。

我们的试验的性能结果

要在昵称上测试 MQT 的性能，我使用适应于联邦环境的部分 "TPC-H" 基准测试工作负载。

我们生成了 4GB TPC-H 数据，并把 TPC-H 表分在两个单独物理机器上的两个数据库中。一个数据库包含表 PART、SUPPLIER、PARTSUPP、NATION 和 REGION，另一个数据库包含包含表 LINEITEM、ORDERS 和 CUSTOMERS。两个数据库都在 AIX® V5.2 on 4-CPU 机器上运行 DB2 Universal Database™ Version 8.2 Fixpack 11。在第三个 AIX V5.2, 4-CPU 机器上，我们安装了 WebSphere Information Integrator Version 8.2 Fixpack 11，并创建了一个联邦数据库。在联邦数据库中，我们定义了服务器来访问这两个 DB2 数据库和昵称，以从这些 DB2 数据库映射 TPC-H 表。


图 1. 我们的试验的配置
利用物化查询表提高 WebSphere Information Integrator 的性能

下一步是为 22 TPC-H 查询中的参数标志插入常量值，以模拟一个典型的工作负载并度量联邦服务器上这些查询的执行时间。

然后我们在这 22 个查询上运行 Design Advisor 以推荐 MQT。使用下面这个命令运行 Design Advisor：


db2advis　-d　tpcdfed　-i　tpch_queries.sql　-m　M　-o　tpch_mqts.sql　-u　-b　tpch_tblsp1　-k　OFF.　　　　
|-------10--------20--------30--------40--------50--------60--------70--------80--------9|　
|--------　XML　error:　The　previous　line　is　longer　than　the　max　of　90　characters　---------|　

通过从将路由到这些 MQT 的 TPC-H 查询添加一些本地谓词，我们定制了推荐的 MQT。

例如，Design Advisor 为查询 #5 推荐以下 MQT：


CREATE　SUMMARY　TABLE　"TEST1　"."MQT2"　AS　(　
SELECT　Q8.C0　AS　"C0",　Q8.C1　AS　"C1",　Q8.C3　AS　"C2",　Q8.C2　AS　"C3"　
FROM　TABLE(　SELECT　Q7.C0　AS　"C0",　SUM(Q7.C1)　AS　"C1",　Q7.C3　AS　"C2",　Q7.C2　AS　"C3"　
　　　　　　FROM　TABLE(　SELECT　Q2.N_NAME　AS　"C0",　
　　　　　　　　　　(Q4.L_EXTENDEDPRICE　*(+1.00000000000000E+000　-　Q4.L_DISCOUNT))　
　　　　　　　　　　AS　"C1",　
　　　　　　　　　　　　　　　　Q5.O_ORDERDATE　AS　"C2",　Q1.R_NAME　AS　"C3"　
　　　　　　　　　　　　FROM　TPCD1.REGION　AS　Q1,TPCD1.NATION　AS　Q2,　
　　　　　　　　　　　　　　　TPCD1.SUPPLIER　AS　Q3,　TPCD2.LINEITEM　AS　Q4,　
　　　　　　　　　　　　　　　TPCD2.ORDERS　AS　Q5,　TPCD2.CUSTOMER　AS　Q6　
　　　　　　　WHERE　(Q2.N_REGIONKEY　=　Q1.R_REGIONKEY)　AND　
　　　　　　　(Q3.S_NATIONKEY　=　Q2.N_NATIONKEY)　
　AND　(Q6.C_NATIONKEY　=　Q3.S_NATIONKEY)　AND　(Q4.L_SUPPKEY　=　Q3.S_SUPPKEY)　
　AND　(Q5.O_ORDERKEY　=　Q4.L_ORDERKEY)　AND　(Q6.C_CUSTKEY　=　Q5.O_CUSTKEY))　AS　Q7　
　　　　　　GROUP　BY　Q7.C2,　Q7.C3,　Q7.C0)　AS　Q8)　
DATA　INITIALLY　DEFERRED　REFRESH　DEFERRED;　

通过添加谓词 R_NAME = 'ASIA' 以便新的 MQT 定义变成下面这样，我们定制了这个 MQT。


CREATE　SUMMARY　TABLE　"TEST1　"."MQT2"　AS　(　
SELECT　Q8.C0　AS　"C0",　Q8.C1　AS　"C1",　Q8.C3　AS　"C2",　Q8.C2　AS　"C3"　
FROM　TABLE(　SELECT　Q7.C0　AS　"C0",　SUM(Q7.C1)　AS　"C1",　Q7.C3　AS　"C2",　Q7.C2　AS　"C3"　
　　　　　FROM　TABLE(　SELECT　Q2.N_NAME　AS　"C0",　
　(Q4.L_EXTENDEDPRICE　*(+1.00000000000000E+000　-　Q4.L_DISCOUNT))　AS　"C1",　
　　　　　　　　　　　　　Q5.O_ORDERDATE　AS　"C2",　Q1.R_NAME　AS　"C3"　
　　　　　　　　FROM　TPCD1.REGION　AS　Q1,TPCD1.NATION　AS　Q2,　
　　　　　　　　　　　　　　　TPCD1.SUPPLIER　AS　Q3,　TPCD2.LINEITEM　AS　Q4,　
　　　　　　　　　　　　　　　TPCD2.ORDERS　AS　Q5,　TPCD2.CUSTOMER　AS　Q6　
　　　　　　　　WHERE　(Q2.N_REGIONKEY　=　Q1.R_REGIONKEY)　AND　
　　　　　　　　(Q3.S_NATIONKEY　=　Q2.N_NATIONKEY)　
　AND　(Q6.C_NATIONKEY　=　Q3.S_NATIONKEY)　AND　(Q4.L_SUPPKEY　=　Q3.S_SUPPKEY)　
　AND　(Q5.O_ORDERKEY　=　Q4.L_ORDERKEY)　AND　(Q6..C_CUSTKEY　=　Q5.O_CUSTKEY　
　AND　(R_NAME　=　'ASIA')))　AS　Q7　
　　　　　　GROUP　BY　Q7.C2,　Q7.C3,　Q7.C0)　AS　Q8)　
DATA　INITIALLY　DEFERRED　REFRESH　DEFERRED;　

接下来，我们在联邦数据库中创建并刷新了 MQT，我们再次运行工作负载以度量将路由到 MQT 的查询的执行时间。

下表总结了试验的结果。Design Advisor 推荐 8 个 MQT，对应于下表所示的 8 个 TPC-H 查询。第一列表示 TPC-H 查询号。下两列表示禁用 MQT 和启用 MQT 时以秒为单位的查询执行时间。第四列展示 MQT 引入的改善，即第二列和第三列之间的时间差异，第五列展示改善的百分比。最后一列列出我们对 Design Advisor 推荐的 MQT 所做的定制。请注意，尽管我们的工作负载被定义为每个查询只使用一个 MQT，但是也可能使用多个 MQT 来回答一个查询。MQT 也可以定义来回答多个查询。

Query #Time w/o MQT（秒）Time w/ MQT（秒）Improvement（秒）Improvement%MQT 定制
325121352%C_MKTSEGMENT =
'BUILDING'
57817799%R_NAME = 'ASIA'
982675%　
1068501826%L_RETURNFLAG = 'R'
12107330%L_SHIPMODE IN
('MAIL','SHIP')
142522392%L_SHIPDATE >= DATE
('1995-09-01')
18205120499%　
19141114099%l_SHIPMODE IN
('AIR', 'AIR REG') AND
L_SHIPINSTRUCT =
'DELIVER IN PERSON'
AND ( P_BRAND =
'BRAND#12' OR
P_BRAND = 'BRAND#23'
OR P_BRAND = 'BRAND#34')
TOTAL5607646483%　

*请注意，这些时间只是提供来演示定义在远程表上的 MQT 所能带来的性能改善。它们不是来自兼容的 TPC-H 基准测试，并且不应该拿来与任何现有的正式结果相比较。

结束语

WebSphere Information Integrator 为战略性信息集成框架提供基础，这样的框架有助于客户将新的应用程序快速推向市场，从现有资产获得更多的回报，并且可控制 IT 成本。随着更多的客户采用数据联邦技术，客户的下一技术焦点通常是获得更高的性能，因为他们的查询的复杂性在增加。

我们考虑物化查询表 (MQT) 支持一个重要的选项来进一步优化联邦服务器的性能。通过基于您的查询工作负载识别一套适当的 MQT，您可以用预先计算的查询结果定义和填充这些 MQT，并通过利用这些 MQT 允许您的查询工作负载运行得更快。正如本文所演示的，有些情况下的改善是相当显著的。

最好的是，您不需要修改应用程序就可以利用 MQT 的优势。联邦查询编译器作出一个基于成本的决策，并使得使用 MQT 的过程对用户是透明的。我们确信，您会发觉该特性是联邦环境中的一个强大工具。

Tags：利用 物化 查询表

编辑录入：爽爽　[复制链接] [打 印]
更多精彩
赞助商链接

热点阅读
焦点图片
最新推荐
精彩阅读