提高商業智慧環境中DB2查詢的效能-專用於資料倉儲和B 的查詢調優方法(2)

方法 1：在事實表與三個維度資料表之間定義適當的參照完整性條件約束

在 DB2 中，可以定義主鍵和外鍵約束，以允許資料庫管理員對資料實施參照完整性條件約束。外鍵等參照約束還有助於提高效能。例如，如果修改 清單 2 中的查詢中的子運算式 TMP1，去掉 PRODUCT_DIM 表上的本地謂詞，那麼，如果在 SALES_FACT.PRODUCT_ID 上建立一個外鍵約束，則最佳化器會消除 SALES_FACT 和 PRODUCT_DIM 之間的串連。如果建立了外鍵約束，則那樣的串連被認為是無損的（lossless），可以從查詢中移除，因為查詢需要從 PRODUCT_DIM 中讀取的資料在 SALES_FACT 表中都有，在 PRODUCT_DIM 與 SALES_FACT 的串連中，只引用到 PRODUCT_DIM 的主鍵，而沒有引用 PRODUCT_DIM 的其它列。

在 星型模式布局 小節中描述的星型模式中，維度中存在的每個 DATE_ID、PRODUCT_ID 和 STORE_ID 在事實表中也必須存在。每個 ID 在維度資料表中都是惟一的，由為每個維度資料表建立的主鍵約束標識。因此，事實表儲存產品被售出時的曆史資料（定量）。下面的表描述了在這種模式中應該建立的主鍵和外鍵。維度中的每個惟一性 ID 在事實表中都有一個相應的外鍵約束。

表	列	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;