提高商业智能环境中DB2查询的性能（2）

方法 1：在事实表与三个维度表之间定义适当的参照完整性约束

在 DB2 中，可以定义主键和外键约束，以允许数据库管理器对数据实施参照完整性约束。外键等参照约束还有助于提高性能。例如，如果修改 清单 2 中的查询中的子表达式 TMP1，去掉 PRODUCT_DIM 表上的本地谓词，那么，如果在 SALES_FACT.PRODUCT_ID 上创建一个外键约束，则优化器会消除 SALES_FACT 和 PRODUCT_DIM 之间的连接。如果创建了外键约束，则那样的连接被认为是无损的（lossless），可以从查询中移除，因为查询需要从 PRODUCT_DIM 中读取的数据在 SALES_FACT 表中都有，在 PRODUCT_DIM 与 SALES_FACT 的连接中，只引用到 PRODUCT_DIM 的主键，而没有引用 PRODUCT_DIM 的其它列。

在 星型模式布局 小节中描述的星型模式中，维度中存在的每个 DATE_ID、PRODUCT_ID 和 STORE_ID 在事实表中也必须存在。每个 ID 在维度表中都是惟一的，由为每个维度表创建的主键约束标识。因此，事实表保存产品被售出时的历史数据（定量）。下面的表描述了在这种模式中应该创建的主键和外键。维度中的每个惟一性 ID 在事实表中都有一个相应的外键约束。

步骤 1A：对事实表执行 ALTER 操作，创建它与维度表之间的适当的 FK 关系。通过上面的表查看事实表与维度表之间的关系。再创建 SALES_FACT 列（DATE_ID,STORE_ID）上的一个索引，以便与 方法 3 中描述的 MDC 方法进行比较，方法 3 使用 (DATE_ID,STORE_ID) 上的一个块索引。

清单 23. 在 SALES_FACT 表中创建外键约束和索引db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log

清单 24.alter_sales_fact.txt 文件的内容CONNECT TO DSS_DB;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX SKAPOOR.IND_DATE_STORE ON SKAPOOR.SALES_FACT (DATE_ID, STORE_ID);
CONNECT RESET;

步骤 1B：收集关于所有表的统计信息：

优化器根据统计信息适当地计算备选查询执行计划（QEP）的成本，并选择最佳计划。在继续下一步骤之前，我们需要收集一些统计信息。

清单 25. 收集关于所有表的统计信息db2 -tvf runstats.ddl -z runstats.log

清单 26. runstats.ddl 的内容CONNECT TO DSS_DB;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;

创建了外键之后，可以看看 DB2 优化器如何利用参照完整性来消除连接。

步骤 1C：解释查询：

清单 27. 含无损连接的查询SELECT
　　　　D.MONTH AS MONTH,
　　　　S.STORE_ID AS STORE_ID,
　　　　S.DISTRICT AS DISTRICT,
　　　　S.REGION AS REGION,
　　　　SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT
　FROM
　　　　SKAPOOR.SALES_FACT F1,
　　　　SKAPOOR.DATE_DIM D,
　　　　SKAPOOR.PRODUCT_DIM P,
　　　　SKAPOOR.STORE_DIM S
　WHERE
　　　　F1.DATE_ID=D.DATE_ID AND
　　　　F1.PRODUCT_ID=P.PRODUCT_ID AND
　　　　F1.STORE_ID=S.STORE_ID AND
　　　　F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
　　　　F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND　　　　
　　　　D.MONTH = 1
　GROUP BY
　　　　S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH)

下面显示了解释此查询的方法之一：

其中 JOIN_ELIM_QUERY.SQL 的内容只包括 清单 27 中的查询，以分号结尾。

可以使用 db2exfmt 工具查看查询执行计划：

输出在 join_elim.txt 中。要获得关于 db2exfmt 工具的详细信息，可以使用 -h 选项。

请打开 下载 小节中的 JOIN_ELIM 文件，看看查询优化器生成的一个访问计划，其中与 PRODUCT_DIM 的连接已经被消除。

可以查看 db2exfmt 输出中的 "Optimized Statement" 部分，注意 PRODUCT_DIM 表已从查询中移除。

注意：使用外键之类的参照约束时，插入、删除和更新操作可能无法正常执行。如果性能对于这些操作来说非常关键，但是连接排除优化在查询中也比较有用，那么可以将外键约束定义为纯信息型（informational） 的。这个方法后面的练习就是针对这一选项的。

步骤 1D：解释和运行整个查询。

为了解释查询，采用与步骤 1C 中相同的步骤：

其中，QUERY1.SQL 的内容只包括 清单 2 中的查询，以分号结尾。

可以使用 db2exfmt 工具查看查询执行计划：

查询执行计划应该类似于 下载 小节中的 Test 1 所提供的查询执行计划。

为了运行查询，要使用 db2batch 工具来评测性能。在此之前，应该让 db2 实例经过一个再循环过程，以便对每种方法进行公平比较，避免其它因素影响性能（例如，后面测试的方法可能受益于之前留下的缓冲池，从而歪曲了评测结果）。

注意：在运行这些测试时，我们的测试系统是空闲的，没有其他活动在运行。

使用 db2stop force 停止 db2，再使用 db2start 重新启动它。使用 db2batch 获得所用时间的信息，如下所示：

文件 test1.results.txt 将包含编译和运行查询所用的时间，如下所示：

练习：

在步骤 1A 中，在 SALES_FACT 表上创建了外键约束，但是，它们可能会影响插入、更新和删除操作，因为数据库管理器必须实施参照完整性。如果这些操作的性能很关键，并且参照完整性可由其它方法来实施，那么可以创建信息型约束，以继续利用连接排除。否则，提供信息型约束会导致不正确的结果。

信息型约束与参照约束的定义类似，只是最后加上了 not enforced 关键字，例如：

ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES DATE_DIM NOT ENFORCED;

接下来，为了完成该练习，还需撤销在 SALES_FACT 表上创建的外键约束，并使用信息约束重复步骤 1A 至 1D。

方法 2：复制维度表上的物化查询表

这里的测试使用的查询和表与方法 1 相同，但是该方法还重复创建维度表上的 MQT。

在方法 1 中，维度表在不同的分区中，必须在分区之间传送数据。可以使用 MQT 将维度表复制到其它分区，以支持合并连接，避免在分区之间发送数据，从而提高查询执行性能。

步骤 2A：创建重复的 MQT：

清单 28. replicated.ddl 文件的内容connect to dss_db;
drop table skapoor.store_dim_rep;
drop table skapoor.product_dim_rep;
drop table skapoor.date_dim_rep;
create table skapoor.store_dim_rep as (select * from skapoor.store_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.product_dim_rep as (select * from skapoor.product_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.date_dim_rep as (select * from skapoor.date_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
refresh table skapoor.store_dim_rep;
refresh table skapoor.product_dim_rep;
refresh table skapoor.dat