方法 1：在事實(shí)表與三個(gè)維度表之間定義適當(dāng)?shù)膮⒄胀暾约s束在 DB2 中，可以定義主鍵和外鍵約束，以答應(yīng)數(shù)據(jù)庫(kù)治理器對(duì)數(shù)據(jù)實(shí)施參照完整性約束。外鍵等參照約束還有助于提高性能。例如，假如修改 清單 2 中的查詢(xún)中的子表達(dá)式 TMP1，去掉 PRODUCT_DIM 表上的本地謂詞，那么，假如在 SALES_FACT.PRODUCT_ID 上創(chuàng)建一個(gè)外鍵約束，則優(yōu)化器會(huì)消除 SALES_FACT 和 PRODUCT_DIM 之間的連接。假如創(chuàng)建了外鍵約束，則那樣的連接被認(rèn)為是無(wú)損的（lossless），可以從查詢(xún)中移除，因?yàn)椴樵?xún)需要從 PRODUCT_DIM 中讀取的數(shù)據(jù)在 SALES_FACT 表中都有，在 PRODUCT_DIM 與 SALES_FACT 的連接中，只引用到 PRODUCT_DIM 的主鍵，而沒(méi)有引用 PRODUCT_DIM 的其它列。在 星型模式布局 小節(jié)中描述的星型模式中，維度中存在的每個(gè) DATE_ID、PRODUCT_ID 和 STORE_ID 在事實(shí)表中也必須存在。每個(gè) ID 在維度表中都是惟一的，由為每個(gè)維度表創(chuàng)建的主鍵約束標(biāo)識(shí)。因此，事實(shí)表保存產(chǎn)品被售出時(shí)的歷史數(shù)據(jù)（定量）。下面的表描述了在這種模式中應(yīng)該創(chuàng)建的主鍵和外鍵。維度中的每個(gè)惟一性 ID 在事實(shí)表中都有一個(gè)相應(yīng)的外鍵約束。表 列 PK/FK 目標(biāo)表（列） DATE_DIMDATE_IDPK無(wú)PRODUCT_DIMPRODUCT_IDPK無(wú)STORE_DIMSTORE_IDPK無(wú)SALES_FACTDATE_IDFKDATE_DIM (DATE_ID)SALES_FACTPRODUCT_IDFKPRODUCT_DIM (PRODUCT_ID)SALES_FACTSTORE_IDFKSTORE_DIM (STORE_ID)步驟 1A：對(duì)事實(shí)表執(zhí)行 ALTER 操作，創(chuàng)建它與維度表之間的適當(dāng)?shù)?FK 關(guān)系。通過(guò)上面的表查看事實(shí)表與維度表之間的關(guān)系。再創(chuàng)建 SALES_FACT 列（DATE_ID,STORE_ID）上的一個(gè)索引，以便與 方法 3 中描述的 MDC 方法進(jìn)行比較，方法 3 使用 (DATE_ID,STORE_ID) 上的一個(gè)塊索引。清單 23. 在 SALES_FACT 表中創(chuàng)建外鍵約束和索引db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log清單 24.alter_sales_fact.txt 文件的內(nèi)容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：收集關(guān)于所有表的統(tǒng)計(jì)信息：優(yōu)化器根據(jù)統(tǒng)計(jì)信息適當(dāng)?shù)赜?jì)算備選查詢(xún)執(zhí)行計(jì)劃（QEP）的成本，并選擇最佳計(jì)劃。在繼續(xù)下一步驟之前，我們需要收集一些統(tǒng)計(jì)信息。清單 25. 收集關(guān)于所有表的統(tǒng)計(jì)信息db2 -tvf runstats.ddl -z runstats.log清單 26. runstats.ddl 的內(nèi)容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;創(chuàng)建了外鍵之后，可以看看 DB2 優(yōu)化器如何利用參照完整性來(lái)消除連接。步驟 1C：解釋查詢(xún)：清單 27. 含無(wú)損連接的查詢(xún)SELECTD.MONTH AS MONTH,S.STORE_ID AS STORE_ID,S.DISTRICT AS DISTRICT,S.REGION AS REGION,SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT　FROMSKAPOOR.SALES_FACT F1,SKAPOOR.DATE_DIM D,SKAPOOR.PRODUCT_DIM P,SKAPOOR.STORE_DIM S　WHEREF1.DATE_ID=D.DATE_ID ANDF1.PRODUCT_ID=P.PRODUCT_ID ANDF1.STORE_ID=S.STORE_ID ANDF1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' ANDF1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD.MONTH = 1　GROUP BYS.STORE_ID,S.DISTRICT,S.REGION,D.MONTH)下面顯示了解釋此查詢(xún)的方法之一：db2 connect to dss_dbdb2 set current explain mode explaindb2 -tvf JOIN_ELIM_QUERY.SQL -z JOIN_ELIM_QUERY.logdb2 set current explain mode nodb2 connect reset其中 JOIN_ELIM_QUERY.SQL 的內(nèi)容只包括 清單 27 中的查詢(xún)，以分號(hào)結(jié)尾。可以使用 db2exfmt 工具查看查詢(xún)執(zhí)行計(jì)劃：db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o join_elim.txt輸出在 join_elim.txt 中。要獲得關(guān)于 db2exfmt 工具的具體信息，可以使用 -h 選項(xiàng)。請(qǐng)打開(kāi) 下載 小節(jié)中的 JOIN_ELIM 文件，看看查詢(xún)優(yōu)化器生成的一個(gè)訪問(wèn)計(jì)劃，其中與 PRODUCT_DIM 的連接已經(jīng)被消除。可以查看 db2exfmt 輸出中的 "Optimized Statement" 部分，注重 PRODUCT_DIM 表已從查詢(xún)中移除。注重：使用外鍵之類(lèi)的參照約束時(shí)，插入、刪除和更新操作可能無(wú)法正常執(zhí)行。假如性能對(duì)于這些操作來(lái)說(shuō)非常要害，但是連接排除優(yōu)化在查詢(xún)中也比較有用，那么可以將外鍵約束定義為純信息型（informational） 的。這個(gè)方法后面的練習(xí)就是針對(duì)這一選項(xiàng)的。步驟 1D：解釋和運(yùn)行整個(gè)查詢(xún)。為了解釋查詢(xún)，采用與步驟 1C 中相同的步驟：db2 connect to dss_dbdb2 set current explain mode explaindb2 -tvf QUERY1.SQL -z QUERY1.logdb2 set current explain mode nodb2 connect reset其中，QUERY1.SQL 的內(nèi)容只包括 清單 2 中的查詢(xún)，以分號(hào)結(jié)尾。可以使用 db2exfmt 工具查看查詢(xún)執(zhí)行計(jì)劃：db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o test1.txt查詢(xún)執(zhí)行計(jì)劃應(yīng)該類(lèi)似于 下載 小節(jié)中的 Test 1 所提供的查詢(xún)執(zhí)行計(jì)劃。為了運(yùn)行查詢(xún)，要使用 db2batch 工具來(lái)評(píng)測(cè)性能。在此之前，應(yīng)該讓 db2 實(shí)例經(jīng)過(guò)一個(gè)再循環(huán)過(guò)程，以便對(duì)每種方法進(jìn)行公平比較，避免其它因素影響性能（例如，后面測(cè)試的方法可能受益于之前留下的緩沖池，從而歪曲了評(píng)測(cè)結(jié)果）。注重：在運(yùn)行這些測(cè)試時(shí)，我們的測(cè)試系統(tǒng)是空閑的，沒(méi)有其他活動(dòng)在運(yùn)行。使用 db2stop force 停止 db2，再使用 db2start 重新啟動(dòng)它。使用 db2batch 獲得所用時(shí)間的信息，如下所示：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 將包含編譯和運(yùn)行查詢(xún)所用的時(shí)間，如下所示：* 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)練習(xí)：在步驟 1A 中，在 SALES_FACT 表上創(chuàng)建了外鍵約束，但是，它們可能會(huì)影響插入、更新和刪除操作，因?yàn)閿?shù)據(jù)庫(kù)治理器必須實(shí)施參照完整性。假如這些操作的性能很要害，并且參照完整性可由其它方法來(lái)實(shí)施，那么可以創(chuàng)建信息型約束，以繼續(xù)利用連接排除。否則，提供信息型約束會(huì)導(dǎo)致不正確的結(jié)果。信息型約束與參照約束的定義類(lèi)似，只是最后加上了 not enforced 要害字，例如：ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES DATE_DIM NOT ENFORCED;接下來(lái)，為了完成該練習(xí)，還需撤銷(xiāo)在 SALES_FACT 表上創(chuàng)建的外鍵約束，并使用信息約束重復(fù)步驟 1A 至 1D。方法 2：復(fù)制維度表上的物化查詢(xún)表這里的測(cè)試使用的查詢(xún)和表與方法 1 相同，但是該方法還重復(fù)創(chuàng)建維度表上的 MQT。在方法 1 中，維度表在不同的分區(qū)中，必須在分區(qū)之間傳送數(shù)據(jù)。可以使用 MQT 將維度表復(fù)制到其它分區(qū)，以支持合并連接，避免在分區(qū)之間發(fā)送數(shù)據(jù)，從而提高查詢(xún)執(zhí)行性能。步驟 2A：創(chuàng)建重復(fù)的 MQT：db2 -tvf replicated.ddl清單 28. replicated.ddl 文件的內(nèi)容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;為了確保可以實(shí)現(xiàn)這種合并，重復(fù)的維度必須與事實(shí)表位于同一數(shù)據(jù)庫(kù)分區(qū)組中。為簡(jiǎn)單起見(jiàn)，我們使用和事實(shí)表一樣的表空間，但是，只要是共用相同的數(shù)據(jù)庫(kù)分區(qū)組，也可以使用不同的表空間。而且，為了使優(yōu)化器在計(jì)算不同備選訪問(wèn)計(jì)劃的成本時(shí)，重復(fù)的表與底層表一致，重復(fù)的表應(yīng)該有與底層表一樣的索引，并且應(yīng)該收集相同的統(tǒng)計(jì)信息。由于不能在 MQT 上創(chuàng)建惟一的索引，所以在底層表的主鍵上創(chuàng)建常規(guī)索引。復(fù)制維度表會(huì)產(chǎn)生該表的一個(gè)額外的副本。在 DB2 9 中，新增了行壓縮功能，以節(jié)省存儲(chǔ)空間。為了減少維度表的額外副本的開(kāi)銷(xiāo)，可以對(duì)其進(jìn)行壓縮。當(dāng)決定使用那樣的技術(shù)時(shí)，建議也壓縮重復(fù)的 MQT。否則，優(yōu)化器可能會(huì)決定執(zhí)行與底層維度表的非合并連接，因?yàn)樗鼈儽粔嚎s過(guò)，在規(guī)模上小于重復(fù)的 MQT。步驟 2B：更新數(shù)據(jù)庫(kù) DSS_DB 的數(shù)據(jù)庫(kù)配置，將 dft_refresh_age 設(shè)置為 "ANY"，以便優(yōu)化器選擇重復(fù)的 MQT：清單 29. 更新數(shù)據(jù)庫(kù)配置db2_all db2 update db cfg for DSS_DB using dft_refresh_age anydb2 terminate步驟 2C：和 方法 1 中的步驟 1C 一樣，生成主查詢(xún)的 db2exfmt 輸出。查看訪問(wèn)計(jì)劃，看重復(fù)的 MQT 是否被訪問(wèn)（也就是說(shuō)，是否選擇了 date_dim_rep、product_dim_rep 和 store_dim_rep）。打開(kāi) 下載 小節(jié)中的 Test 2，看看這個(gè)訪問(wèn)計(jì)劃的一個(gè)例子。在上述訪問(wèn)計(jì)劃中，不存在方法 1 中那樣的連接之間的表隊(duì)列（TQ）操作符，因?yàn)閮?yōu)化器選擇使用重復(fù)的維度表，從而答應(yīng)合并連接。步驟 2D：確認(rèn)訪問(wèn)計(jì)劃中會(huì)訪問(wèn) MQT 之后，像 方法 1 中的步驟 1D 那樣，使用 db2batch 工具評(píng)測(cè)性能。在運(yùn)行 db2batch 之前，應(yīng)確保 db2 實(shí)例經(jīng)過(guò)再循環(huán)過(guò)程。然后，記錄下結(jié)果。注重：對(duì)于該方法，要將數(shù)據(jù)庫(kù)配置參數(shù) DFT_REFRESH_AGE 設(shè)置為 ANY on all Database Partitions。假如想再次運(yùn)行方法 1 中的測(cè)試，則需要將 DFT_REFRESH_AGE 數(shù)據(jù)庫(kù)配置參數(shù)更新為 "0"。否則，就會(huì)使用重復(fù)的 MQT，而不是使用基本維度表。練習(xí)1、使用行壓縮來(lái)壓縮基本維度表 STORE_DIM、PRODUCT_DIM 和 DATE_DIM。您將需要重新收集所有這三個(gè)維度表的統(tǒng)計(jì)信息。重新收集好統(tǒng)計(jì)信息后，重復(fù)步驟 2C 至 2D。2、假如優(yōu)化器沒(méi)有選擇訪問(wèn)第一個(gè)練習(xí)中的重復(fù) MQT，則重復(fù)這個(gè)練習(xí)，并壓縮重復(fù)的 MQT。方法 3：使用重復(fù)的維度上的 MQT 的 MDC 事實(shí)表這個(gè)測(cè)試類(lèi)似于 方法 2，但是用一個(gè) MDC 事實(shí)表替代了 SALES_FACT 表。MDC 提供了自動(dòng)集群表中多個(gè)維上的數(shù)據(jù)的自動(dòng)化方法，假如選擇了適當(dāng)?shù)木S度列和 EXTENTSIZE 大小，可以顯著提供查詢(xún)性能。步驟 3A：計(jì)算 EXTENTSIZE 大小。這里為表空間選擇 12 作為 EXTENTSIZE 大小，計(jì)算方法如下：請(qǐng)參閱 Info Center 中的指南，獲得 MDC 表維度方面的幫助，這里選擇 (date_id,store_id) 列作為 MDC 表的維度。下面的查詢(xún)用于計(jì)算 sales_fact 表中 (date_id, store_id) 的惟一組合的數(shù)量：清單 30. 計(jì)算 (date_id, store_id) 惟一組合的數(shù)量的查詢(xún)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下面的查詢(xún)計(jì)算平均每單元行數(shù)（RPC）、最小每單元行數(shù)以及最大每單元行數(shù)。清單 31. 確定評(píng)價(jià)行數(shù)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)SELECTAVG(RPC) AS RPC, MIN(RPC) AS MINRPC, MAX(RPC) AS MAXRPCFROM CELL_TABLE;RPC MINRPC　MAXRPC----------- ----------- -----------298　 1 380　1 record(s) selected.為了計(jì)算每個(gè)單元的間距，我們使用 DB2 9 治理指南中 Space requirements for user table data 小節(jié)中的以下公式。數(shù)據(jù)庫(kù)中用于每個(gè)用戶(hù)表的 4KB 頁(yè)面的數(shù)量可以這樣來(lái)估計(jì)。首先，確定平均行長(zhǎng)度。在我們的例子中，列采用固定數(shù)據(jù)類(lèi)型，因此可以將每個(gè)列的長(zhǎng)度相加，得到行的長(zhǎng)度。可以使用下面的 DESCRIBE 語(yǔ)句獲得列長(zhǎng)度：清單 32. DESCRIBE 語(yǔ)句DB2 DESCRIBE SELECT * FROM SALES_FACTSQLDA Informationsqldaid : SQLDA sqldabc: 896　sqln: 20　sqld: 6Column Informationsqltype　 sqllen　sqlname.datasqlname.length--------------------　------　------------------------------　--------------385　 DATE10　DATE_ID　7497　 INTEGER　4　PRODUCT_ID　10497　 INTEGER　4　STORE_ID 8497　 INTEGER　4　QUANTITY 8497　 INTEGER　4　PRICE5453　 CHARACTER　100　TRANSACTION_DETAILS 8在 DESCRIBE 語(yǔ)句的結(jié)果中，"sqllen" 列表明每個(gè)列的長(zhǎng)度。計(jì)算每頁(yè)平均記錄數(shù)量的公式為：RECORDS_PER_PAGE = ROUND DOWN( 4028 / (AVG ROW SIZE + 10))在我們的例子中，AVG ROW SIZE = 126 字節(jié)（列長(zhǎng)度的總和：10+4+4+4+4+100）。因此，RECORDS_PER_PAGE = ROUND DOWN (4028 / (126+10)) = 29。RECORDS_PER_PAGE 公式中額外的 10 個(gè)字節(jié)用于開(kāi)銷(xiāo)。存儲(chǔ) 298 條記錄（清單 31 中的 RPC）所需的 4K 頁(yè)面的數(shù)量可以這樣計(jì)算：NUMBER_OF_PAGES = (NUMBER_OF_RECORDS / RECORDS_PER_PAGE) * 1.1 where NUMBER_OF_RECORDS = RPC=298NUMBER_OF_PAGES = ( 298 records / 29 records per page ) * 1.1 = 11.3 ~ 12 4K pages因此，EXTENTSIZE 為 12。步驟 3B：創(chuàng)建 EXTENTSIZE 大小為 12 的 MDC 表空間：清單 33. 創(chuàng)建 MDC 表空間db2 -tvf mdc_tablespace.ddl -z mdc_tablespace.log清單 34. mdc_tablespace.ddl 的內(nèi)容CREATE REGULAR TABLESPACE FACT_SMS_MDC_EX IN DATABASE PARTITION GROUPFACT_GROUP PAGESIZE 4096 MANAGED BY SYSTEMUSING ('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：創(chuàng)建 MDC 表清單 35. 創(chuàng)建 MDC 表db2 -tvf sales_fact_mdc.ddl -z sales_fact_mdc.log清單 36. sales_fact_mdc.ddl 文件的內(nèi)容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 表的塊索引是在事實(shí)表維列（date_id, store_id）上自動(dòng)創(chuàng)建的。步驟 3D：將數(shù)據(jù)插入 MDC 表。在我們的測(cè)試環(huán)境中，將數(shù)據(jù)插入 MDC 表大約花了 4 個(gè)小時(shí)。清單 37. 將數(shù)據(jù)插入 MDC 表db2 -tvf sales_fact_mdc_insert_alter.ddl -z sales_fact_mdc_insert.log清單 38. sales_fact_mdc_insert_alter.ddl 的內(nèi)容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_1ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES SKAPOOR.DATE_DIM;ALTER TABLE SKAPOOR.SALES_FACT_MDC_1ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY (STORE_ID) REFERENCES SKAPOOR.STORE_DIM;ALTER TABLE SKAPOOR.SALES_FACT_MDC_1ADD 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 中的查詢(xún)，將表名從 "SALES_FACT" 改為 "SALES_FACT_MDC_1"，以測(cè)試 MDC 的優(yōu)點(diǎn)。下面的清單 39 描述了新的查詢(xún)。像方法 1 的步驟 1C 一樣，以解釋模式編譯該查詢(xún)，并生成主查詢(xún)的 db2exfmt 輸出。檢查訪問(wèn)計(jì)劃是否使用了 MDC 索引，并且看上去像 下載 小節(jié)中的 Test 3。清單 39. MDC 查詢(xún)WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS( SELECTD.MONTH AS MONTH,S.STORE_ID AS STORE_ID,S.DISTRICT AS DISTRICT,S.REGION AS REGION,SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT　FROMSKAPOOR.SALES_FACT_MDC_1 F1,SKAPOOR.DATE_DIM D,SKAPOOR.PRODUCT_DIM P,SKAPOOR.STORE_DIM S　WHEREP.MODEL LIKE '%model%' ANDF1.DATE_ID=D.DATE_ID ANDF1.PRODUCT_ID=P.PRODUCT_ID ANDF1.STORE_ID=S.STORE_ID ANDF1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' ANDF1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD.MONTH = 1　GROUP BYS.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS(SELECTD1.MONTH AS MONTH,S1.STORE_ID AS STORE_ID,S1.DISTRICT AS DISTRICT,S1.REGION AS REGION,SUM(F2.QUANTITY * F2.PRICE) AS AMOUNTFROMSKAPOOR.SALES_FACT_MDC_1 F2,SKAPOOR.DATE_DIM D1,SKAPOOR.PRODUCT_DIM P1,SKAPOOR.STORE_DIM S1WHEREP1.MODEL LIKE '%model%' ANDF2.DATE_ID=D1.DATE_ID ANDF2.PRODUCT_ID=P1.PRODUCT_ID ANDF2.STORE_ID=S1.STORE_ID ANDF2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' ANDF2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD1.MONTH=11GROUP BYS1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)SELECTA.*,B.*FROMTMP1 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 那樣，將實(shí)例再循環(huán)，然后使用 db2batch 工具評(píng)測(cè)性能。注重：QUERY1.SQL 文件中的查詢(xún)被更改，以反映 清單 39 中的查詢(xún)。記錄下結(jié)果。方法 4：表分區(qū)和重復(fù)的維度上的 MQT這個(gè)測(cè)試類(lèi)似于 方法 2，但是用一個(gè)表分區(qū)事實(shí)表替代了 SALES_FACT 表。表分區(qū)是 DB2 9 中的新功能。它是一種數(shù)據(jù)組織模式，按照這種模式，根據(jù)一個(gè)或多個(gè)表列中的值，表數(shù)據(jù)被劃分到多個(gè)被稱(chēng)作數(shù)據(jù)分區(qū)的存儲(chǔ)對(duì)象中。每個(gè)數(shù)據(jù)分區(qū)是一個(gè)單獨(dú)的物理實(shí)體，可以在不同的表空間中，也可以在相同的表空間中，或者兩者相結(jié)合。這種模式對(duì)于 BI 環(huán)境中非常大的表比較有益，它可以簡(jiǎn)化數(shù)據(jù)的轉(zhuǎn)入（roll-in）和轉(zhuǎn)出（roll-out），根據(jù)應(yīng)用的謂詞避免掃描不需要訪問(wèn)的分區(qū)，從而提高查詢(xún)執(zhí)行效率。步驟 4A：創(chuàng)建分區(qū)表第一步是確定適當(dāng)?shù)姆謪^(qū)范圍。日期經(jīng)常用于作為分區(qū)范圍，因此我們將根據(jù) SALES_FACT 的 DATE_ID 列對(duì)表進(jìn)行分區(qū)。Info Center 提供了關(guān)于定義分區(qū)表范圍的更多具體信息。 由于 SALES_FACT 表由全年的事務(wù)組成，而我們的查詢(xún)是比較各個(gè)月份的銷(xiāo)售量，因此每個(gè)范圍由一個(gè)月的數(shù)據(jù)組成。為了演示分區(qū)表對(duì)數(shù)據(jù)轉(zhuǎn)入的簡(jiǎn)化作用，先從包含一月份這個(gè)范圍的分區(qū)表開(kāi)始，然后附加表示接下來(lái)每個(gè)月的分區(qū)：清單 40. 創(chuàng)建分區(qū) SALES_FACT 表db2 -tvf tablepart.ddl -z tablepart.log清單 41. tablepart.ddl 的內(nèi)容CONNECT TO DSS_DB;CREATE REGULAR TABLESPACE FACT_TPART_SMS IN DATABASE PARTITION GROUP FACT_GROUPPAGESIZE 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 JANUARYCREATE 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 JANINSERT 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 TABLECREATE 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 MONTHINSERT 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 MarchALTER 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 visibleSET INTEGRITY FOR "SKAPOOR"."SALES_FACT_TPART"IMMEDIATE CHECKED;commit work;-- Attach SALES from April to JuneALTER 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 DecALTER 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;yocommit work;VALUES(CURRENT TIMESTAMP);RUNSTATS ON TABLE SKAPOOR.SALES_FACT_TPART WITH DISTRIBUTION;commit work;CONNECT RESET;在我們的測(cè)試環(huán)境中，填充所有分區(qū)花了大約 3 個(gè)小時(shí)。而將表附加（attach）到每個(gè)分區(qū)則比較快。表一旦被附加到分區(qū)之后，它就成為 SALES_FACT_TPART 表的一個(gè)物理實(shí)體，不能再將其當(dāng)作單獨(dú)的表來(lái)查詢(xún)。假如想那樣做的話(huà)，必須將表與 SALES_FACT_TPART 表分離開(kāi)來(lái)。步驟 4B：修改 清單 2 中的查詢(xún)，將表名從 "SALES_FACT" 改為 "SALES_FACT_TPART"，以測(cè)試分區(qū)消除的優(yōu)點(diǎn)。下面的清單 42 描述了這個(gè)新的查詢(xún)。像 方法 1 的步驟 1C 那樣，以解釋模式編譯該查詢(xún)，并生成主查詢(xún)的 db2exfmt 輸出。檢查訪問(wèn)計(jì)劃是否使用了分區(qū)表，這就像 下載 小節(jié)中的 TPART。清單 42. 分區(qū)表查詢(xún)WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS( SELECTD.MONTH AS MONTH,S.STORE_ID AS STORE_ID,S.DISTRICT AS DISTRICT,S.REGION AS REGION,SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT　FROMSKAPOOR.SALES_FACT_TPART F1,SKAPOOR.DATE_DIM D,SKAPOOR.PRODUCT_DIM P,SKAPOOR.STORE_DIM S　WHEREP.MODEL LIKE '%model%' ANDF1.DATE_ID=D.DATE_ID ANDF1.PRODUCT_ID=P.PRODUCT_ID ANDF1.STORE_ID=S.STORE_ID ANDF1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' ANDF1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD.MONTH = 1　GROUP BYS.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS(SELECTD1.MONTH AS MONTH,S1.STORE_ID AS STORE_ID,S1.DISTRICT AS DISTRICT,S1.REGION AS REGION,SUM(F2.QUANTITY * F2.PRICE) AS AMOUNTFROMSKAPOOR.SALES_FACT_TPART F2,SKAPOOR.DATE_DIM D1,SKAPOOR.PRODUCT_DIM P1,SKAPOOR.STORE_DIM S1WHEREP1.MODEL LIKE '%model%' ANDF2.DATE_ID=D1.DATE_ID ANDF2.PRODUCT_ID=P1.PRODUCT_ID ANDF2.STORE_ID=S1.STORE_ID ANDF2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' ANDF2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD1.MONTH=11GROUP BYS1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)SELECTA.*,B.*FROMTMP1 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 輸出中，關(guān)于分區(qū)表訪問(wèn)的具體信息表明是否發(fā)生了分區(qū)排除以及訪問(wèn)了哪些分區(qū)：14) TBSCAN: (Table Scan)Cumulative Total Cost: 15378Cumulative CPU Cost: 8.77067e+008Cumulative I/O Cost: 15213Cumulative Re-Total Cost: 15378Cumulative Re-CPU Cost: 8.77065e+008Cumulative Re-I/O Cost: 15213Cumulative First Row Cost: 8.22883Cumulative Comm Cost:0Cumulative First Comm Cost:0Estimated Bufferpool Buffers: 15213Arguments:---------DPESTFLG: (Number of data partitions accessed are Estimated)FALSEDPLSTPRT: (List of data partitions accessed)10DPNUMPRT: (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 參數(shù)指示是估計(jì)分區(qū)消除（TRUE）還是在編譯時(shí)精確計(jì)算分區(qū)消除（FALSE）。假如已估計(jì)，那么在運(yùn)行時(shí)確定實(shí)際的分區(qū)消除。在這個(gè)例子中，分區(qū)消除是在編譯時(shí)計(jì)算的。DPLSTPRT 參數(shù)指示訪問(wèn)哪些分區(qū)，DPNUMPRT 指示所訪問(wèn)的分區(qū)的數(shù)量。假如 DPESTFLG 為 TRUE，那么這兩個(gè)值由優(yōu)化器估算。在這個(gè)例子中，只有一個(gè)分區(qū)，即分區(qū) 10 被訪問(wèn)。其余分區(qū)被忽略。DP Elim Predicates 部分列出了用于確定訪問(wèn)哪些分區(qū)的謂詞。步驟 4C：像 方法 1 中的步驟 1D 那樣，對(duì)實(shí)例進(jìn)行再循環(huán)，并使用 db2batch 工具評(píng)測(cè)性能。注重：QUERY1.SQL 文件中的查詢(xún)被修改，以反映清單 42 中的查詢(xún)。記錄下結(jié)果。練習(xí)1、索引可以幫助提高使用分區(qū)表 SALES_FACT_TPART 的查詢(xún)的性能。創(chuàng)建一個(gè)或多個(gè)可能有用的索引，并重復(fù)步驟 4B 和 4C。別忘了收集關(guān)于索引的統(tǒng)計(jì)信息。2、試著將一個(gè)或多個(gè)分區(qū)與 SALES_FACT_TPART 表分離開(kāi)來(lái)，感覺(jué)一下如何通過(guò)使用分區(qū)表輕松而有效地轉(zhuǎn)出數(shù)據(jù)。方法 5：表分區(qū)、MDC 和重復(fù)的維度上的 MQT這個(gè)測(cè)試類(lèi)似于 方法 4，但是用一個(gè)分區(qū) MDC 事實(shí)表替代了 SALES_FACT_TPART 表。可以將 MDC 和表分區(qū)相結(jié)合，進(jìn)一步提高查詢(xún)的性能。與 方法 3 采用了相同的技術(shù)，使用 DATE_ID 和 STORE_ID 列作為維列，采用了與方法 4 一樣的范圍和 DATE_ID。步驟 5A：創(chuàng)建分區(qū) MDC 表清單 43. 創(chuàng)建分區(qū) MDC SALES_FACT 表db2 -tvf tablepart_mdc.ddl -z tablepart_mdc.log清單 44. tablepart_mdc.ddl 的內(nèi)容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 中的查詢(xún)，將表名從 "SALES_FACT" 改為 "SALES_FACT_TPART_MDC"，以測(cè)試將 MDC 與分區(qū)消除相結(jié)合的優(yōu)點(diǎn)。下面的清單 45 描述了這個(gè)新的查詢(xún)。像 方法 1 的步驟 1C 那樣，以解釋模式編譯該查詢(xún)，并生成主查詢(xún)的 db2exfmt 輸出。檢查訪問(wèn)計(jì)劃是否使用了分區(qū)表和塊索引，它看上去像 下載 小節(jié)中的 TPART_MDC 一樣 。清單 45. 分區(qū) MDC 表查詢(xún)WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS( SELECTD.MONTH AS MONTH,S.STORE_ID AS STORE_ID,S.DISTRICT AS DISTRICT,S.REGION AS REGION,SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT　FROMSKAPOOR.SALES_FACT_TPART_MDC F1,SKAPOOR.DATE_DIM D,SKAPOOR.PRODUCT_DIM P,SKAPOOR.STORE_DIM S　WHEREP.MODEL LIKE '%model%' ANDF1.DATE_ID=D.DATE_ID ANDF1.PRODUCT_ID=P.PRODUCT_ID ANDF1.STORE_ID=S.STORE_ID ANDF1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' ANDF1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD.MONTH = 1　GROUP BYS.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS(SELECTD1.MONTH AS MONTH,S1.STORE_ID AS STORE_ID,S1.DISTRICT AS DISTRICT,S1.REGION AS REGION,SUM(F2.QUANTITY * F2.PRICE) AS AMOUNTFROMSKAPOOR.SALES_FACT_TPART_MDC F2,SKAPOOR.DATE_DIM D1,SKAPOOR.PRODUCT_DIM P1,SKAPOOR.STORE_DIM S1WHEREP1.MODEL LIKE '%model%' ANDF2.DATE_ID=D1.DATE_ID ANDF2.PRODUCT_ID=P1.PRODUCT_ID ANDF2.STORE_ID=S1.STORE_ID ANDF2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' ANDF2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) ANDD1.MONTH=11GROUP BYS1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)SELECTA.*,B.*FROMTMP1 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 那樣，將實(shí)例再循環(huán)，然后使用 db2batch 工具評(píng)測(cè)性能。注重：QUERY1.SQL 文件中的查詢(xún)被更改，以反映清單 39 中的查詢(xún)。記錄下結(jié)果。方法 6：使用 MQT 預(yù)先計(jì)算聚合結(jié)果這個(gè)測(cè)試類(lèi)似于 方法 1，但是增加 MQT，以便預(yù)先計(jì)算聚合值。使用 MQT 物化表達(dá)為聚合的結(jié)果可以顯著提高查詢(xún)性能。在 清單 2 中描述的每個(gè)查詢(xún)中，向外連接的每個(gè)分支由相同連接上的一個(gè)聚合組成。惟一的不同是應(yīng)用于事實(shí)表的本地謂詞。假如可以在執(zhí)行查詢(xún)之前預(yù)先計(jì)算連接，則可以顯著提高查詢(xún)執(zhí)行性能。步驟 6A：創(chuàng)建和刷新 MQT清單 46. 創(chuàng)建 MQT 表db2 -tvf mqt2.ddl -z mqt2.log清單 47. mqt2.ddl 文件的內(nèi)容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 中創(chuàng)建的兩個(gè)索引 MQT2_IND3 和 MQT2_IND4 用于提高從 MQT 訪問(wèn)數(shù)據(jù)的性能。步驟 6B： 收集關(guān)于 MQT 統(tǒng)計(jì)信息，并調(diào)整模式，以符合您的環(huán)境：清單 48. 收集關(guān)于 MQT 表的統(tǒng)計(jì)信息DB2 RUNSTATS ON TABLE SKAPOOR.MQT2 WITH DISTRIBUTION AND INDEXES ALL步驟 6C：像 方法 1 的 STEP 1C 那樣，解釋 清單 2 中的查詢(xún)，并生成 db2exfmt 輸出。然后，查看訪問(wèn)計(jì)劃。應(yīng)該可以看到，訪問(wèn)計(jì)劃選擇 MQT 和 MQT2，并使用一個(gè)連接操作符，以完成兩個(gè) MQT 的向外連接。這個(gè)訪問(wèn)計(jì)劃看上去應(yīng)該類(lèi)似于 下載 小節(jié)中的 Test 6。假如沒(méi)有選擇 MQT，則應(yīng)確保在所有數(shù)據(jù)庫(kù)分區(qū)上的數(shù)據(jù)庫(kù)配置中 DFT_REFRESH_AGE 被設(shè)為 "ANY"；否則，優(yōu)化器不會(huì)考慮 MQT。STEP 6D: 像 方法 1 中的步驟 1D 那樣，將實(shí)例再循環(huán)，并使用 db2batch 工具評(píng)測(cè)性能。現(xiàn)在，記錄下結(jié)果。考察每種方法對(duì)查詢(xún)執(zhí)行性能的效果注重：所有測(cè)試都是在沒(méi)有其它其他活動(dòng)在運(yùn)行的環(huán)境中執(zhí)行的。下面的表列出了在我們的系統(tǒng)上使用 db2batch 工具測(cè)到的每種方法所用的時(shí)間（單位為秒）。方法 查詢(xún) 所用時(shí)間（秒） 1. 參照完整性約束清單 2115.002. 重復(fù)的 MQT清單 2103.423. 多維集群和重復(fù)的 MQT清單 3938.364. 表分區(qū)和重復(fù)的 MQT清單 42197.745. 表分區(qū)、MDC 和重復(fù)的 MQT清單 4532.216. 使用 MQT 預(yù)先計(jì)算聚合結(jié)果清單 27.61結(jié)果表明，使用 MQT 預(yù)先計(jì)算聚合結(jié)果可以提高查詢(xún)性能的效果最為顯著。與 方法 1 中基本的星型模式布局相比，多維集群，以及表分區(qū)與 MDC 的組合，也可以顯著提高查詢(xún)性能。在我們的環(huán)境中，重復(fù)的維度表可以略微提高性能。這是因?yàn)樗?4 個(gè)數(shù)據(jù)庫(kù)分區(qū)都是邏輯分區(qū)，是在同一臺(tái)物理機(jī)器上創(chuàng)建的。假如為數(shù)據(jù)庫(kù)分區(qū)使用多臺(tái)物理機(jī)器，那么這種方法應(yīng)該可以顯著提高性能，尤其是當(dāng)數(shù)據(jù)庫(kù)分區(qū)之間需要大量傳送數(shù)據(jù)時(shí)，這種方法的效果尤為明顯。表分區(qū)本身實(shí)際上會(huì)使性能變得更糟。我們的測(cè)試中未創(chuàng)建任何索引來(lái)比較分區(qū)消除。事實(shí)表上的附加謂詞進(jìn)一步過(guò)濾向外連接每個(gè)分支中訪問(wèn)的分區(qū)。在表上創(chuàng)建一個(gè)或多個(gè)索引的另一個(gè)優(yōu)點(diǎn)是可以取得更好的性能。這是 方法 4 中留給讀者的一個(gè)練習(xí)。這些測(cè)試表明，使用 DB2 9 中的各種特性可以提高 BI 查詢(xún)的性能。結(jié)束語(yǔ)本文中討論的這些方法只是提高 BI 環(huán)境中查詢(xún)性能的一部分方法。請(qǐng)動(dòng)手完成下一小節(jié)及本文各處所提供的練習(xí)。