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

dn001 2009-05-01 13:04:05

　　方法 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 在事实表中都有一个相应的外键约束。

表	列	PK/FK	目标表（列）
DATE_DIM	DATE_ID	PK	无
PRODUCT_DIM	PRODUCT_ID	PK	无
STORE_DIM	STORE_ID	PK	无
SALES_FACT	DATE_ID	FK	DATE_DIM (DATE_ID)
SALES_FACT	PRODUCT_ID	FK	PRODUCT_DIM (PRODUCT_ID)
SALES_FACT	STORE_ID	FK	STORE_DIM (STORE_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)

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

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf JOIN_ELIM_QUERY.SQL -z JOIN_ELIM_QUERY.log
db2 set current explain mode no
db2 connect reset

　　其中 JOIN_ELIM_QUERY.SQL 的内容只包括清单 27 中的查询，以分号结尾。　　可以使用 db2exfmt 工具查看查询执行计划：db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o join_elim.txt　　输出在 join_elim.txt 中。要获得关于 db2exfmt 工具的具体信息，可以使用 -h 选项。　　请打开下载小节中的 JOIN_ELIM 文件，看看查询优化器生成的一个访问计划，其中与 PRODUCT_DIM 的连接已经被消除。　　可以查看 db2exfmt 输出中的 "Optimized Statement" 部分，注重 PRODUCT_DIM 表已从查询中移除。　　注重：使用外键之类的参照约束时，插入、删除和更新操作可能无法正常执行。假如性能对于这些操作来说非常要害，但是连接排除优化在查询中也比较有用，那么可以将外键约束定义为纯信息型（informational）的。这个方法后面的练习就是针对这一选项的。　　步骤 1D：解释和运行整个查询。　　为了解释查询，采用与步骤 1C 中相同的步骤：

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset

　　其中，QUERY1.SQL 的内容只包括清单 2 中的查询，以分号结尾。　　可以使用 db2exfmt 工具查看查询执行计划：db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o test1.txt　　查询执行计划应该类似于下载小节中的 Test 1 所提供的查询执行计划。　　为了运行查询，要使用 db2batch 工具来评测性能。在此之前，应该让 db2 实例经过一个再循环过程，以便对每种方法进行公平比较，避免其它因素影响性能（例如，后面测试的方法可能受益于之前留下的缓冲池，从而歪曲了评测结果）。　　注重：在运行这些测试时，我们的测试系统是空闲的，没有其他活动在运行。　　使用 db2stop force 停止 db2，再使用 db2start 重新启动它。使用 db2batch 获得所用时间的信息，如下所示：db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt　　　　文件 test1.results.txt 将包含编译和运行查询所用的时间，如下所示：

* Prepare Time is:　　　 7.278206 seconds
* Execute Time is:　　 107.729436 seconds
* Fetch Time is:　　　　 0.000102 seconds
* Elapsed Time is:　　 115.007744 seconds (complete)

　　练习：　　在步骤 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：db2 -tvf replicated.ddl　　清单 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.date_dim_rep;
create index skapoor.store_dim_id_rep on skapoor.store_dim_rep (store_id);
create index skapoor.product_dim_id_rep on skapoor.product_dim_rep (product_id);
create index skapoor.date_dim_id_rep on skapoor.date_dim_rep (date_id);
runstats on table skapoor.store_dim_rep with distribution and indexes all;
runstats on table skapoor.product_dim_rep with distribution and indexes all;
runstats on table skapoor.date_dim_rep with distribution and indexes all;
connect reset;

　　为了确保可以实现这种合并，重复的维度必须与事实表位于同一数据库分区组中。为简单起见，我们使用和事实表一样的表空间，但是，只要是共用相同的数据库分区组，也可以使用不同的表空间。而且，为了使优化器在计算不同备选访问计划的成本时，重复的表与底层表一致，重复的表应该有与底层表一样的索引，并且应该收集相同的统计信息。由于不能在 MQT 上创建惟一的索引，所以在底层表的主键上创建常规索引。　　复制维度表会产生该表的一个额外的副本。在 DB2 9 中，新增了行压缩功能，以节省存储空间。为了减少维度表的额外副本的开销，可以对其进行压缩。当决定使用那样的技术时，建议也压缩重复的 MQT。否则，优化器可能会决定执行与底层维度表的非合并连接，因为它们被压缩过，在规模上小于重复的 MQT。　　步骤 2B：更新数据库 DSS_DB 的数据库配置，将 dft_refresh_age 设置为 "ANY"，以便优化器选择重复的 MQT：　　清单 29. 更新数据库配置

db2_all db2 update db cfg for DSS_DB using dft_refresh_age any
db2 terminate

　　步骤 2C：和方法 1 中的步骤 1C 一样，生成主查询的 db2exfmt 输出。查看访问计划，看重复的 MQT 是否被访问（也就是说，是否选择了 date_dim_rep、product_dim_rep 和 store_dim_rep）。打开下载小节中的 Test 2，看看这个访问计划的一个例子。　　在上述访问计划中，不存在方法 1 中那样的连接之间的表队列（TQ）操作符，因为优化器选择使用重复的维度表，从而答应合并连接。　　步骤 2D：确认访问计划中会访问 MQT 之后，像方法 1 中的步骤 1D 那样，使用 db2batch 工具评测性能。在运行 db2batch 之前，应确保 db2 实例经过再循环过程。然后，记录下结果。　　注重：对于该方法，要将数据库配置参数 DFT_REFRESH_AGE 设置为 ANY on all Database Partitions。假如想再次运行方法 1 中的测试，则需要将 DFT_REFRESH_AGE 数据库配置参数更新为 "0"。否则，就会使用重复的 MQT，而不是使用基本维度表。　　练习　　1、使用行压缩来压缩基本维度表 STORE_DIM、PRODUCT_DIM 和 DATE_DIM。您将需要重新收集所有这三个维度表的统计信息。重新收集好统计信息后，重复步骤 2C 至 2D。　　2、假如优化器没有选择访问第一个练习中的重复 MQT，则重复这个练习，并压缩重复的 MQT。　　方法 3：使用重复的维度上的 MQT 的 MDC 事实表　　这个测试类似于方法 2，但是用一个 MDC 事实表替代了 SALES_FACT 表。MDC 提供了自动集群表中多个维上的数据的自动化方法，假如选择了适当的维度列和 EXTENTSIZE 大小，可以显著提供查询性能。　　步骤 3A：计算 EXTENTSIZE 大小。　　这里为表空间选择 12 作为 EXTENTSIZE 大小，计算方法如下：　　请参阅 Info Center 中的指南，获得 MDC 表维度方面的帮助，这里选择 (date_id,store_id) 列作为 MDC 表的维度。　　下面的查询用于计算 sales_fact 表中 (date_id, store_id) 的惟一组合的数量：　　清单 30. 计算 (date_id, store_id) 惟一组合的数量的查询

WITH TMP (DATE_ID, STORE_ID) AS
　(SELECT DISTINCT DATE_ID, STORE_ID FROM SALES_FACT)
SELECT COUNT(*) AS CELL_COUNT FROM TMP;
CELL_COUNT
-----------
　　　73097

　　下面的查询计算平均每单元行数（RPC）、最小每单元行数以及最大每单元行数。　　清单 31. 确定评价行数

WITH CELL_TABLE(DATE_ID,STORE_ID,RPC) AS
(
　 SELECT DISTINCT DATE_ID,STORE_ID, COUNT(*)
　　 FROM SALES_FACT
　 GROUP BY DATE_ID,STORE_ID
)
SELECT
　　AVG(RPC) AS RPC, MIN(RPC) AS MINRPC, MAX(RPC) AS MAXRPC
FROM CELL_TABLE;
RPC　　　　 MINRPC　　　MAXRPC
----------- ----------- -----------
　　　　298　　　　　 1　　　　 380
　1 record(s) selected.

　　为了计算每个单元的间距，我们使用 DB2 9 治理指南中 Space requirements for user table data 小节中的以下公式。　　数据库中用于每个用户表的 4KB 页面的数量可以这样来估计。首先，确定平均行长度。在我们的例子中，列采用固定数据类型，因此可以将每个列的长度相加，得到行的长度。可以使用下面的 DESCRIBE 语句获得列长度：　　清单 32. DESCRIBE 语句

DB2 DESCRIBE SELECT * FROM SALES_FACT
SQLDA Information
sqldaid : SQLDA　　 sqldabc: 896　sqln: 20　sqld: 6
Column Information
sqltype　　　　　　　 sqllen　sqlname.data　　　　　　　　　　sqlname.length
--------------------　------　------------------------------　--------------
385　 DATE　　　　　　　　10　DATE_ID　　　　　　　　　　　　　　　　　　　7
497　 INTEGER　　　　　　　4　PRODUCT_ID　　　　　　　　　　　　　　　　　10
497　 INTEGER　　　　　　　4　STORE_ID　　　　　　　　　　　　　　　　　　 8
497　 INTEGER　　　　　　　4　QUANTITY　　　　　　　　　　　　　　　　　　 8
497　 INTEGER　　　　　　　4　PRICE　　　　　　　　　　　　　　　　　　　　5
453　 CHARACTER　　　　　100　TRANSACTION_DETAILS　　　　　　　　　　　　　　　　　　 8

　　在 DESCRIBE 语句的结果中，"sqllen" 列表明每个列的长度。　　计算每页平均记录数量的公式为：　　RECORDS_PER_PAGE = ROUND DOWN( 4028 / (AVG ROW SIZE + 10))　　在我们的例子中，AVG ROW SIZE = 126 字节（列长度的总和：10+4+4+4+4+100）。　　因此，RECORDS_PER_PAGE = ROUND DOWN (4028 / (126+10)) = 29。　　RECORDS_PER_PAGE 公式中额外的 10 个字节用于开销。　　存储 298 条记录（清单 31 中的 RPC）所需的 4K 页面的数量可以这样计算：　　NUMBER_OF_PAGES = (NUMBER_OF_RECORDS / RECORDS_PER_PAGE) * 1.1 where NUMBER_OF_RECORDS = RPC=298　　NUMBER_OF_PAGES = ( 298 records / 29 records per page ) * 1.1 = 11.3 ~ 12 4K pages　　因此，EXTENTSIZE 为 12。　　步骤 3B：创建 EXTENTSIZE 大小为 12 的 MDC 表空间：　　清单 33. 创建 MDC 表空间db2 -tvf mdc_tablespace.ddl -z mdc_tablespace.log　　清单 34. mdc_tablespace.ddl 的内容

CREATE REGULAR TABLESPACE FACT_SMS_MDC_EX IN DATABASE PARTITION GROUP
FACT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('d:databasefact_tbsp_mdc_EX120') ON DBPARTITIONNUMS (0)
USING ('d:databasefact_tbsp_mdc_EX121') ON DBPARTITIONNUMS (1)
USING ('d:databasefact_tbsp_mdc_EX122') ON DBPARTITIONNUMS (2)
USING ('d:databasefact_tbsp_mdc_EX123') ON DBPARTITIONNUMS (3)
　　 EXTENTSIZE 12
　　 PREFETCHSIZE 24
　　 BUFFERPOOL IBMDEFAULTBP
　　 OVERHEAD 7.500000
　　 TRANSFERRATE 0.060000
　　 NO FILE SYSTEM CACHING　
　　 DROPPED TABLE RECOVERY ON;

　　步骤 3C：创建 MDC 表　　清单 35. 创建 MDC 表db2 -tvf sales_fact_mdc.ddl -z sales_fact_mdc.log　　清单 36. sales_fact_mdc.ddl 文件的内容

CONNECT TO DSS_DB;
---------------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT_MDC_1"
---------------------------------------------------------
CREATE TABLE "SKAPOOR "."SALES_FACT_MDC_1"　(
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"RESERVE" CHAR(100) )　
　　　　 DISTRIBUTE BY HASH("DATE_ID")　
　　　　　 IN "FACT_SMS_MDC_EX"　
　　　　 ORGANIZE BY (
　　　　　( "DATE_ID" ) ,
　　　　　( "STORE_ID" ) )
　　　　 ;
COMMIT WORK;
CONNECT RESET;

　　注重：用于 MDC 表的块索引是在事实表维列（date_id, store_id）上自动创建的。　　步骤 3D：将数据插入 MDC 表。在我们的测试环境中，将数据插入 MDC 表大约花了 4 个小时。　　清单 37. 将数据插入 MDC 表db2 -tvf sales_fact_mdc_insert_alter.ddl -z sales_fact_mdc_insert.log　　清单 38. sales_fact_mdc_insert_alter.ddl 的内容

CONNECT TO DSS_DB;
VALUES(CURRENT TIMESTAMP);
-----------------------------------
-- SET OPTLEVEL 0 TO FAVOUR INDEX ACCESS TO IMPROVE PERFORMANCE OF INSERT.
SET CURRENT QUERY OPTIMIZATION 0;
-----------------------------------
-- INSERTING THE DATA IN THE ORDER OF THE MDC COLUMNS IMPROVES
-- THE PERFORMANCE OF THE INSERT.
INSERT INTO SKAPOOR.SALES_FACT_MDC_1 SELECT *
FROM SKAPOOR.SALES_FACT ORDER BY DATE_ID,STORE_ID;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES SKAPOOR.DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY (STORE_ID) REFERENCES SKAPOOR.STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT_MDC_1
ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY (PRODUCT_ID)
REFERENCES SKAPOOR.PRODUCT_DIM;
VALUES(CURRENT TIMESTAMP);
RUNSTATS ON TABLE SKAPOOR.SALES_FACT_MDC_1 WITH DISTRIBUTION AND INDEXES ALL;

　　步骤 3E：修改清单 2 中的查询，将表名从 "SALES_FACT" 改为 "SALES_FACT_MDC_1"，以测试 MDC 的优点。下面的清单 39 描述了新的查询。像方法 1 的步骤 1C 一样，以解释模式编译该查询，并生成主查询的 db2exfmt 输出。检查访问计划是否使用了 MDC 索引，并且看上去像下载小节中的 Test 3。　　清单 39. MDC 查询

WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( 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_MDC_1 F1,
　　　　SKAPOOR.DATE_DIM D,
　　　　SKAPOOR.PRODUCT_DIM P,
　　　　SKAPOOR.STORE_DIM S
　WHERE
　　　　P.MODEL LIKE '%model%' AND
　　　　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) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(　　SELECT
　　　　D1.MONTH AS MONTH,
　　　　S1.STORE_ID AS STORE_ID,
　　　　S1.DISTRICT AS DISTRICT,
　　　　S1.REGION AS REGION,
　　　　SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
　　　　
　　FROM
　　　　SKAPOOR.SALES_FACT_MDC_1 F2,
　　　　SKAPOOR.DATE_DIM D1,
　　　　SKAPOOR.PRODUCT_DIM P1,
　　　　SKAPOOR.STORE_DIM S1
　　WHERE
　　　　P1.MODEL LIKE '%model%' AND
　　　　F2.DATE_ID=D1.DATE_ID AND
　　　　F2.PRODUCT_ID=P1.PRODUCT_ID AND
　　　　F2.STORE_ID=S1.STORE_ID AND
　　　　F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
　　　　F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
　　　　D1.MONTH=11
　　GROUP BY
　　　　S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)
SELECT
　　A.*,
　　B.*
FROM
　　TMP1 A LEFT OUTER JOIN TMP2 B ON
　　　　　(A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

　　步骤 3F：像方法 1 中的步骤 1D 那样，将实例再循环，然后使用 db2batch 工具评测性能。　　注重：QUERY1.SQL 文件中的查询被更改，以反映清单 39 中的查询。记录下结果。　　方法 4：表分区和重复的维度上的 MQT　　这个测试类似于方法 2，但是用一个表分区事实表替代了 SALES_FACT 表。表分区是 DB2 9 中的新功能。它是一种数据组织模式，按照这种模式，根据一个或多个表列中的值，表数据被划分到多个被称作数据分区的存储对象中。每个数据分区是一个单独的物理实体，可以在不同的表空间中，也可以在相同的表空间中，或者两者相结合。这种模式对于 BI 环境中非常大的表比较有益，它可以简化数据的转入（roll-in）和转出（roll-out），根据应用的谓词避免扫描不需要访问的分区，从而提高查询执行效率。　　步骤 4A：创建分区表　　第一步是确定适当的分区范围。日期经常用于作为分区范围，因此我们将根据 SALES_FACT 的 DATE_ID 列对表进行分区。Info Center 提供了关于定义分区表范围的更多具体信息。由于 SALES_FACT 表由全年的事务组成，而我们的查询是比较各个月份的销售量，因此每个范围由一个月的数据组成。　　为了演示分区表对数据转入的简化作用，先从包含一月份这个范围的分区表开始，然后附加表示接下来每个月的分区：　　清单 40. 创建分区 SALES_FACT 表db2 -tvf tablepart.ddl -z tablepart.log　　清单 41. tablepart.ddl 的内容

CONNECT TO DSS_DB;
CREATE REGULAR TABLESPACE FACT_TPART_SMS IN DATABASE PARTITION GROUP FACT_GROUP
PAGESIZE 4096 MANAGED BY SYSTEM
　　 USING ('d:databasefact_tpart_tbsp0') ON DBPARTITIONNUMS (0)
　　 USING ('d:databasefact_tpart_tbsp1') ON DBPARTITIONNUMS (1)
　　 USING ('d:databasefact_tpart_tbsp2') ON DBPARTITIONNUMS (2)
　　 USING ('d:databasefact_tpart_tbsp3') ON DBPARTITIONNUMS (3)
　　 EXTENTSIZE 32
　　 PREFETCHSIZE AUTOMATIC
　　 BUFFERPOOL IBMDEFAULTBP
　　 OVERHEAD 7.500000
　　 TRANSFERRATE 0.060000
　　 NO FILE SYSTEM CACHING;
-- CREATE THE SALES_FACT TABLE PARTITIONED ON DATE_ID
-- WITH A SINGLE PARTITION TO START WITH, CONTAINING
-- ALL SALES FROM JANUARY
CREATE TABLE "SKAPOOR "."SALES_FACT_TPART"　(
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")　
　　　　　　　　　 IN "FACT_TPART_SMS"
　　　　　　　　 PARTITION BY ("DATE_ID")
　　　　　　　　　　(PART Jan STARTING ('1/1/2006') ENDING ('1/31/2006'))

VALUES (CURRENT TIMESTAMP);
-- POPULATE THE SALES FROM JAN
INSERT INTO "SKAPOOR"."SALES_FACT_TPART"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '1/1/2006' AND '1/31/2006';
commit work;
VALUES (CURRENT TIMESTAMP);
-- CREATE TABLES FOR SALES FROM EACH MONTH
-- WHICH WILL THEN BE ATTACHED TO SALES_FACT_TPART TABLE
CREATE TABLE "SKAPOOR"."SALES_FEB" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAR" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_APR" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_MAY" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUN" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_JUL" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_AUG" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_SEP" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_OCT" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_NOV" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
CREATE TABLE "SKAPOOR"."SALES_DEC" (
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )
　　　　 DISTRIBUTE BY HASH("DATE_ID")
　　　　　　　　　 IN "FACT_TPART_SMS";
VALUES (CURRENT TIMESTAMP);
-- POPULATE EACH TABLE WITH SALES FOR THE CORRESPONDING MONTH
INSERT INTO "SKAPOOR"."SALES_FEB"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '2/1/2006' AND '2/28/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_MAR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '3/1/2006' AND '3/31/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_APR"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '4/1/2006' AND '4/30/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_MAY"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '5/1/2006' AND '5/31/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_JUN"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '6/1/2006' AND '6/30/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_JUL"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '7/1/2006' AND '7/31/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_AUG"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '8/1/2006' AND '8/31/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_SEP"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '9/1/2006' AND '9/30/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_OCT"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '10/1/2006' AND '10/31/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_NOV"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '11/1/2006' AND '11/30/2006';
commit work;
INSERT INTO "SKAPOOR"."SALES_DEC"
SELECT * FROM "SKAPOOR"."SALES_FACT"
WHERE DATE_ID BETWEEN '12/1/2006' AND '12/31/2006';
commit work;
VALUES (CURRENT TIMESTAMP);
-- Attach SALES from February and March
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Feb STARTING FROM '2/1/2006' ENDING AT '2/28/2006'
　FROM "SKAPOOR"."SALES_FEB";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Mar STARTING FROM '3/1/2006' ENDING AT '3/31/2006'
　FROM "SKAPOOR"."SALES_MAR";
-- Make the partitions visible
SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;
commit work;
-- Attach SALES from April to June
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Apr STARTING FROM '4/1/2006' ENDING AT '4/30/2006'
　FROM "SKAPOOR"."SALES_APR";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION May STARTING FROM '5/1/2006' ENDING AT '5/31/2006'
　FROM "SKAPOOR"."SALES_MAY";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Jun STARTING FROM '6/1/2006' ENDING AT '6/30/2006'
　FROM "SKAPOOR"."SALES_JUN";
SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;
commit work;
-- Attach SALES from July to Dec
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Jul STARTING FROM '7/1/2006' ENDING AT '7/31/2006'
　FROM "SKAPOOR"."SALES_JUL";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Aug STARTING FROM '8/1/2006' ENDING AT '8/31/2006'
　FROM "SKAPOOR"."SALES_AUG";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Sep STARTING FROM '9/1/2006' ENDING AT '9/30/2006'
　FROM "SKAPOOR"."SALES_SEP";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Oct STARTING FROM '10/1/2006' ENDING AT '10/31/2006'
　FROM "SKAPOOR"."SALES_OCT";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Nov STARTING FROM '11/1/2006' ENDING AT '11/30/2006'
　FROM "SKAPOOR"."SALES_NOV";
ALTER TABLE "SKAPOOR"."SALES_FACT_TPART"
　ATTACH PARTITION Dec STARTING FROM '12/1/2006' ENDING AT '12/31/2006'
　FROM "SKAPOOR"."SALES_DEC";
SET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"
IMMEDIATE CHECKED;
yo
commit work;
VALUES(CURRENT TIMESTAMP);
RUNSTATS ON TABLE SKAPOOR.SALES_FACT_TPART WITH DISTRIBUTION;
commit work;
CONNECT RESET;

　　在我们的测试环境中，填充所有分区花了大约 3 个小时。而将表附加（attach）到每个分区则比较快。表一旦被附加到分区之后，它就成为 SALES_FACT_TPART 表的一个物理实体，不能再将其当作单独的表来查询。假如想那样做的话，必须将表与 SALES_FACT_TPART 表分离开来。　　步骤 4B：修改清单 2 中的查询，将表名从 "SALES_FACT" 改为 "SALES_FACT_TPART"，以测试分区消除的优点。下面的清单 42 描述了这个新的查询。像方法 1 的步骤 1C 那样，以解释模式编译该查询，并生成主查询的 db2exfmt 输出。检查访问计划是否使用了分区表，这就像下载小节中的 TPART。　　清单 42. 分区表查询

WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( 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_TPART F1,
　　　　SKAPOOR.DATE_DIM D,
　　　　SKAPOOR.PRODUCT_DIM P,
　　　　SKAPOOR.STORE_DIM S
　WHERE
　　　　P.MODEL LIKE '%model%' AND
　　　　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) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(　　SELECT
　　　　D1.MONTH AS MONTH,
　　　　S1.STORE_ID AS STORE_ID,
　　　　S1.DISTRICT AS DISTRICT,
　　　　S1.REGION AS REGION,
　　　　SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
　　　　
　　FROM
　　　　SKAPOOR.SALES_FACT_TPART F2,
　　　　SKAPOOR.DATE_DIM D1,
　　　　SKAPOOR.PRODUCT_DIM P1,
　　　　SKAPOOR.STORE_DIM S1
　　WHERE
　　　　P1.MODEL LIKE '%model%' AND
　　　　F2.DATE_ID=D1.DATE_ID AND
　　　　F2.PRODUCT_ID=P1.PRODUCT_ID AND
　　　　F2.STORE_ID=S1.STORE_ID AND
　　　　F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
　　　　F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
　　　　D1.MONTH=11
　　GROUP BY
　　　　S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)
SELECT
　　A.*,
　　B.*
FROM
　　TMP1 A LEFT OUTER JOIN TMP2 B ON
　　　　　(A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

　　在 db2exfmt 输出中，关于分区表访问的具体信息表明是否发生了分区排除以及访问了哪些分区：

　　14) TBSCAN: (Table Scan)
　　　　Cumulative Total Cost: 　　　　15378
　　　　Cumulative CPU Cost: 　　　　8.77067e+008
　　　　Cumulative I/O Cost: 　　　　15213
　　　　Cumulative Re-Total Cost: 　　15378
　　　　Cumulative Re-CPU Cost: 　　8.77065e+008
　　　　Cumulative Re-I/O Cost: 　　15213
　　　　Cumulative First Row Cost: 　　8.22883
　　　　Cumulative Comm Cost:　　　　0
　　　　Cumulative First Comm Cost:　　0
　　　　Estimated Bufferpool Buffers: 　　15213
　　　　Arguments:
　　　　---------
　　　　DPESTFLG: (Number of data partitions accessed are Estimated)
　　　　　　FALSE
　　　　DPLSTPRT: (List of data partitions accessed)
　　　　　　10
　　　　DPNUMPRT: (Number of data partitions accessed)
　　　　　　1
　　　　　　
　　...
　　　　DP Elim Predicates:
　　　　------------------
　　　　Range 1)
　　　　　　Stop　Predicate: (Q10.DATE_ID <= '11/30/2006')
　　　　　　Start Predicate: ('11/01/2006' <= Q10.DATE_ID)
　　　　...

　　DPESTFLG 参数指示是估计分区消除（TRUE）还是在编译时精确计算分区消除（FALSE）。假如已估计，那么在运行时确定实际的分区消除。在这个例子中，分区消除是在编译时计算的。DPLSTPRT 参数指示访问哪些分区，DPNUMPRT 指示所访问的分区的数量。假如 DPESTFLG 为 TRUE，那么这两个值由优化器估算。在这个例子中，只有一个分区，即分区 10 被访问。其余分区被忽略。　　DP Elim Predicates 部分列出了用于确定访问哪些分区的谓词。　　步骤 4C：像方法 1 中的步骤 1D 那样，对实例进行再循环，并使用 db2batch 工具评测性能。　　注重：QUERY1.SQL 文件中的查询被修改，以反映清单 42 中的查询。记录下结果。　　练习　　1、索引可以帮助提高使用分区表 SALES_FACT_TPART 的查询的性能。创建一个或多个可能有用的索引，并重复步骤 4B 和 4C。别忘了收集关于索引的统计信息。　　2、试着将一个或多个分区与 SALES_FACT_TPART 表分离开来，感觉一下如何通过使用分区表轻松而有效地转出数据。　　方法 5：表分区、MDC 和重复的维度上的 MQT　　这个测试类似于方法 4，但是用一个分区 MDC 事实表替代了 SALES_FACT_TPART 表。可以将 MDC 和表分区相结合，进一步提高查询的性能。与方法 3 采用了相同的技术，使用 DATE_ID 和 STORE_ID 列作为维列，采用了与方法 4 一样的范围和 DATE_ID。　　步骤 5A：创建分区 MDC 表　　清单 43. 创建分区 MDC SALES_FACT 表db2 -tvf tablepart_mdc.ddl -z tablepart_mdc.log　　清单 44. tablepart_mdc.ddl 的内容

CONNECT TO DSS_DB;
CREATE TABLE "SKAPOOR "."SALES_FACT_TPART_MDC" ( "DATE_ID" DATE ,
"PRODUCT_ID" INTEGER ,
"STORE_ID" INTEGER ,
"QUANTITY" INTEGER ,
"PRICE" INTEGER ,
"TRANSACTION_DETAILS" CHAR(100))
DISTRIBUTE BY HASH("DATE_ID")　
　PARTITION BY RANGE("DATE_ID")
　(PART "JAN" STARTING('2006-01-01') ENDING('2006-01-31') IN "FACT_TPART_SMS",
　 PART "FEB" STARTING('2006-02-01') ENDING('2006-02-28') IN "FACT_TPART_SMS",
　 PART "MAR" STARTING('2006-03-01') ENDING('2006-03-31') IN "FACT_TPART_SMS",
　 PART "APR" STARTING('2006-04-01') ENDING('2006-04-30') IN "FACT_TPART_SMS",
　 PART "MAY" STARTING('2006-05-01') ENDING('2006-05-31') IN "FACT_TPART_SMS",
　 PART "JUN" STARTING('2006-06-01') ENDING('2006-06-30') IN "FACT_TPART_SMS",
　 PART "JUL" STARTING('2006-07-01') ENDING('2006-07-31') IN "FACT_TPART_SMS",
　 PART "AUG" STARTING('2006-08-01') ENDING('2006-08-31') IN "FACT_TPART_SMS",
　 PART "SEP" STARTING('2006-09-01') ENDING('2006-09-30') IN "FACT_TPART_SMS",
　 PART "OCT" STARTING('2006-10-01') ENDING('2006-10-31') IN "FACT_TPART_SMS",
　 PART "NOV" STARTING('2006-11-01') ENDING('2006-11-30') IN "FACT_TPART_SMS",
　 PART "DEC" STARTING('2006-12-01') ENDING('2006-12-31') IN "FACT_TPART_SMS")
ORGANIZE BY (
　　　　DATE_ID,
　　STORE_ID)

COMMIT WORK ;
INSERT INTO SKAPOOR.SALES_FACT_TPART_MDC
　SELECT * FROM SKAPOOR.SALES_FACT_MDC_1;
COMMIT WORK;
RUNSTATS ON TABLE SKAPORR.SALES_FACT_TPART_MDC WITH DISTRIBUTION AND INDEXES ALL;
COMMIT WORK;
CONNECT RESET;

　　步骤 5B：修改清单 2 中的查询，将表名从 "SALES_FACT" 改为 "SALES_FACT_TPART_MDC"，以测试将 MDC 与分区消除相结合的优点。下面的清单 45 描述了这个新的查询。像方法 1 的步骤 1C 那样，以解释模式编译该查询，并生成主查询的 db2exfmt 输出。检查访问计划是否使用了分区表和块索引，它看上去像下载小节中的 TPART_MDC 一样。　　清单 45. 分区 MDC 表查询

WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( 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_TPART_MDC F1,
　　　　SKAPOOR.DATE_DIM D,
　　　　SKAPOOR.PRODUCT_DIM P,
　　　　SKAPOOR.STORE_DIM S
　WHERE
　　　　P.MODEL LIKE '%model%' AND
　　　　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) ,
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
(　　SELECT
　　　　D1.MONTH AS MONTH,
　　　　S1.STORE_ID AS STORE_ID,
　　　　S1.DISTRICT AS DISTRICT,
　　　　S1.REGION AS REGION,
　　　　SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
　　　　
　　FROM
　　　　SKAPOOR.SALES_FACT_TPART_MDC F2,
　　　　SKAPOOR.DATE_DIM D1,
　　　　SKAPOOR.PRODUCT_DIM P1,
　　　　SKAPOOR.STORE_DIM S1
　　WHERE
　　　　P1.MODEL LIKE '%model%' AND
　　　　F2.DATE_ID=D1.DATE_ID AND
　　　　F2.PRODUCT_ID=P1.PRODUCT_ID AND
　　　　F2.STORE_ID=S1.STORE_ID AND
　　　　F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
　　　　F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND
　　　　D1.MONTH=11
　　GROUP BY
　　　　S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)
SELECT
　　A.*,
　　B.*
FROM
　　TMP1 A LEFT OUTER JOIN TMP2 B ON
　　　　　(A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

　　步骤 5C：像方法 1 中的步骤 1D 那样，将实例再循环，然后使用 db2batch 工具评测性能。　　注重：QUERY1.SQL 文件中的查询被更改，以反映清单 39 中的查询。记录下结果。　　方法 6：使用 MQT 预先计算聚合结果　　这个测试类似于方法 1，但是增加 MQT，以便预先计算聚合值。使用 MQT 物化表达为聚合的结果可以显著提高查询性能。在清单 2 中描述的每个查询中，向外连接的每个分支由相同连接上的一个聚合组成。惟一的不同是应用于事实表的本地谓词。假如可以在执行查询之前预先计算连接，则可以显著提高查询执行性能。　　步骤 6A：创建和刷新 MQT　　清单 46. 创建 MQT 表db2 -tvf mqt2.ddl -z mqt2.log　　清单 47. mqt2.ddl 文件的内容

CONNECT TO DSS_DB;
------------------------------------------------
-- DDL STATEMENTS FOR TABLE "SKAPOOR "."MQT2"
CREATE TABLE SKAPOOR.MQT2 AS
(
　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 ,
　　 F1.DATE_ID
　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
　　 P.MODEL LIKE '%MODEL%'
　GROUP BY S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH, F1.DATE_ID
)
DATA INITIALLY DEFERRED REFRESH DEFERRED IN FACT_SMS;
REFRESH TABLE "SKAPOOR "."MQT2";
-- DDL STATEMENTS FOR INDEXES ON TABLE "SKAPOOR "."MQT2"
CREATE INDEX "SKAPOOR "."MQT2_IND3" ON "SKAPOOR "."MQT2"
　　　　("MONTH" ASC,
　　　　 "DATE_ID" ASC)
　　　　ALLOW REVERSE SCANS;
-- DDL STATEMENTS FOR INDEXES ON TABLE "SKAPOOR "."MQT2"
CREATE INDEX "SKAPOOR "."MQT2_IND4" ON "SKAPOOR "."MQT2"
　　　　("DATE_ID" ASC,
　　　　 "STORE_ID" ASC,
　　　　 "DISTRICT" ASC,
　　　　 "REGION" ASC,
　　　　 "MONTH" ASC,
　　　　 "AMOUNT" ASC)
　　　　ALLOW REVERSE SCANS;

　　清单 41 中创建的两个索引 MQT2_IND3 和 MQT2_IND4 用于提高从 MQT 访问数据的性能。　　步骤 6B：收集关于 MQT 统计信息，并调整模式，以符合您的环境：　　清单 48. 收集关于 MQT 表的统计信息DB2 RUNSTATS ON TABLE SKAPOOR.MQT2 WITH DISTRIBUTION AND INDEXES ALL　　步骤 6C：像方法 1 的 STEP 1C 那样，解释清单 2 中的查询，并生成 db2exfmt 输出。然后，查看访问计划。应该可以看到，访问计划选择 MQT 和 MQT2，并使用一个连接操作符，以完成两个 MQT 的向外连接。这个访问计划看上去应该类似于下载小节中的 Test 6。　　假如没有选择 MQT，则应确保在所有数据库分区上的数据库配置中 DFT_REFRESH_AGE 被设为 "ANY"；否则，优化器不会考虑 MQT。　　STEP 6D: 像方法 1 中的步骤 1D 那样，将实例再循环，并使用 db2batch 工具评测性能。　　现在，记录下结果。　　考察每种方法对查询执行性能的效果　　注重：所有测试都是在没有其它其他活动在运行的环境中执行的。　　下面的表列出了在我们的系统上使用 db2batch 工具测到的每种方法所用的时间（单位为秒）。

方法	查询	所用时间（秒）
1. 参照完整性约束	清单 2	115.00
2. 重复的 MQT	清单 2	103.42
3. 多维集群和重复的 MQT	清单 39	38.36
4. 表分区和重复的 MQT	清单 42	197.74
5. 表分区、MDC 和重复的 MQT	清单 45	32.21
6. 使用 MQT 预先计算聚合结果	清单 2	7.61

　　结果表明，使用 MQT 预先计算聚合结果可以提高查询性能的效果最为显著。与方法 1 中基本的星型模式布局相比，多维集群，以及表分区与 MDC 的组合，也可以显著提高查询性能。　　在我们的环境中，重复的维度表可以略微提高性能。这是因为所有 4 个数据库分区都是逻辑分区，是在同一台物理机器上创建的。假如为数据库分区使用多台物理机器，那么这种方法应该可以显著提高性能，尤其是当数据库分区之间需要大量传送数据时，这种方法的效果尤为明显。　　表分区本身实际上会使性能变得更糟。我们的测试中未创建任何索引来比较分区消除。事实表上的附加谓词进一步过滤向外连接每个分支中访问的分区。在表上创建一个或多个索引的另一个优点是可以取得更好的性能。这是方法 4 中留给读者的一个练习。　　这些测试表明，使用 DB2 9 中的各种特性可以提高 BI 查询的性能。　　结束语　　本文中讨论的这些方法只是提高 BI 环境中查询性能的一部分方法。请动手完成下一小节及本文各处所提供的练习。

标签：查询