提高商业智能环境中DB2查询的性能（2）
2007-05-20 16:22:13 来源：WEB开发网方法 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 环境中查询性能的一部分方法。请动手完成下一小节及本文各处所提供的练习。
- 好的评价 如果觉得好，就请您
0%(0) 
- 差的评价 如果觉得差，就请您
0%(0) 
中查找“提高商业智能环境中DB2查询的性能（2）”更多相关内容
中查找“提高商业智能环境中DB2查询的性能（2）”更多相关内容
赞助商链接
