方法 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 環境中查詢性能的一部分方法。請動手完成下一小節及本文各處所提供的練習。
