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

专用于数据仓库和 BI 的查询调优方法

高效地运行大型查询,是商业智能环境中的顶级性能挑战。学习在这种环境中提高 IBM® DB2® 数据服务器查询性能的技巧。逐步了解各种不同的方法,然后在自己的系统上进行试验。将每种方法应用于一条 SQL 语句,并使用 db2batch 工具评测性能。

简介

本文主要讨论可以使决策支持系统(DSS)中的大型查询高效地执行的一些方法。这些查询通常都是访问较多数据的单纯 select 查询。下面是我们要讨论的一些方法:

  1. 建立适当的参照完整性约束
  2. 使用物化查询表(MQT)将表复制到其它数据库分区,以允许非分区键列上的合并连接
  3. 使用多维集群(MDC)
  4. 使用表分区(DB2® 9 的新功能)
  5. 结合使用表分区和多维集群
  6. 使用 MQT 预先计算聚合结果

本文中的例子针对 Windows 平台上运行的 DB2 9。但是,其中的概念和信息对于任何平台都是有用的。由于大多数商业智能(BI)环境都使用 DB2 Database Partitioning Feature(DPF,DB2 数据库分区特性),我们的例子也使用 DPF 将数据划分到多个物理和逻辑分区之中。


数据库布局和设置

本节描述用于在我们的系统上执行测试的数据库的物理和逻辑布局。

星型模式布局

本文使用如下所示的星型模式:

清单 1. 星型模式
                                  PRODUCT_DIM             DATE_DIM
                                            \            /
                                             \          /
                                              SALES_FACT
                                                  |
                                                  |
		                             STORE_DIM

其中的表的定义如下:

表名类型列名数据类型列描述
SALES_FACTFACT TABLEDATE_IDDATE产品售出日期
PRODUCT_IDINT所购买产品的标识符
STORE_IDINT出售产品的商店的标识符
QUANTITYINT这次交易中售出产品的数量
PRICEINT产品购买价格。[为了简单起见,该字段为整型,但是使用小数型更符合实际]
TRANSACTION_DETAILSCHAR(100)关于此次交易的描述/详细信息
DATE_DIMDIMENSION TABLEDATE_ID NOT NULLDATE惟一标识符
MONTHINT日期记录所属的月份
QUARTERINT日期记录所属的季度(第 1、第 2、第 3 或第 4 季度)
YEARINT日期记录所属的年份
PRODUCT_DIMDIMENSION TABLEPRODUCT_ID NOT NULLINT产品惟一标识符
PRODUCT_DESCCHAR(20)对产品的描述
MODELCHAR(200)产品型号
MAKECHAR(50)产品的质地
STORE_DIMDIMENSION TABLESTORE_ID NOT NULLINT商店惟一标识符
LOCATIONCHAR(15)商店位置
DISTRICTCHAR(15)商店所属街区
REGIONCHAR(15)商店所属区域

事实表 SALES_FACT 包含 2006 年的总体销售信息。它包括产品售出日期、产品 ID、销售该产品的商店的 ID、售出的特定产品的数量,以及产品的价格。事实表中还添加了 TRANSACTION_DETAILS 列,以便在从事实表中访问数据时生成更多的 I/O。

维度表 DATE_DIM 包含商店开放期间的惟一的日期和相应的月份、季度和年份信息。

维度表 PRODUCT_DIM 包含公司所销售的不同产品。每种产品有一个惟一的产品 ID 和一个产品描述、型号以及质地。

维度表 STORE_DIM 包含不同的商店 ID 和商店的位置、所属街区以及所属区域等信息。

数据库分区信息
数据库分区组名数据库分区数
FACT_GROUP0,1,2,3
DATE_GROUP1
PRODUCT_GROUP2
STORE_GROUP3

各表都位于它自己的分区组中。3 个维度表都比较小,所以它们位于一个数据库分区上。而事实表则跨 4 个分区。

表空间信息
表空间名数据库分区组
FACT_SMSFACT_GROUPSALES_FACT
DATE_SMSDATE_GROUPDATE_DIM
PRODUCT_SMSPRODUCT_GROUPPRODUCT_DIM
STORE_SMSSTORE_GROUPSTORE_DIM

各表都位于自己的表空间中。还有一种常见的方法是将这 3 个维度表放在同一个表空间中。

缓冲池信息

本文中的测试所使用的默认缓冲池是 IBMDEFAULTBP,该缓冲池由 1,000 个 4K 的页面组成。在本文的测试中,所有表空间共享这个缓冲池。在通常的 BI 环境中,会创建不同的缓冲池。

主查询

下面的查询用于测试本文中讨论的各种不同的方法。该查询执行一个向外连接,比较二月份和十一月份 10 家商店的销售信息。

清单 2. 主查询 [Query1.sql]
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 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 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;

环境设置

本文的测试是使用以下环境执行的:

清单 3. db2level
DB2 9 Enterprise Edition:

DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
清单 4. 操作系统
System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3
清单 5. 硬件
CPU: total:2 online:2 Cores per socket:1 Threading degree per core:2
Physical Memory(MB): total:2551 free:1988 available:1949
Virtual  Memory(MB): total:4950 free:6575
Swap     Memory(MB): total:2399 free:4587
1 Physical disk Size 100GB

空间需求

为了重新创建本文中描述的所有测试用例,需要高达 20Gb 的磁盘空间来存放数据和日志文件。其中将近 13Gb 的空间要分配给日志文件。我们要使用循环日志记录,分配 100 个主日志:

清单 6. 用于日志的数据库配置
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150

略加修改为事实表填充数据的脚本,即可减少日志文件所需的磁盘空间。本文的后面将对此进行讨论。


设置数据库

第一步是创建一个测试数据库。

在本文的测试中,创建了 4 个逻辑数据分区。在 etc\services 文件中,应确保有足够的端口用于创建 4 个数据分区。在我们的测试环境中,文件 C:\WINDOWS\system32\drivers\etc\services 中包含关于实例 "DB2" 的以下内容:

清单 7. services 文件的内容
DB2_DB2           60000/tcp
DB2_DB2_1         60001/tcp
DB2_DB2_2         60002/tcp
DB2_DB2_END       60003/tcp
DB2c_DB2          50000/tcp

为向实例添加数据库分区,可使用 DB2 CLP 执行以下命令:

清单 8. 使用 db2ncrt 命令创建数据库分区
db2stop 
db2ncrt /n:1 /u:username,password /i:DB2 /m:machine /p:1
db2ncrt /n:2 /u:username,password /i:DB2 /m:machine /p:2
db2ncrt /n:3 /u:username,password /i:DB2 /m:machine /p:3

其中 /u 选项所表示的用户名和密码,/m 选项所表示的计算机名,以及 /i 选项所表示的实例名应该根据您自己的环境加以修改。

创建数据库

创建数据库 DSS_DB。这里使用 D: 盘存储该数据库。请根据您自己的环境进行调整。

清单 9. 创建数据库的命令
db2 create database dss_db on D:\;

数据库和数据库管理器是使用下面的设置来配置的。db2_all 工具用于设置所有数据库分区上的数据库配置和数据库管理器配置。

清单 10. 更新数据库管理器配置的语句
db2_all update dbm cfg \
     using cpuspeed 2.282997e-007 intra_parallel NO comm_bandwidth 100.000000

db2_all update db cfg for DSS_DB \
     using locklist 2450 dft_degree 1 maxlocks 60 \
           avg_appls 1 stmtheap 16384 dft_queryopt 5

创建数据库分区组和表空间

使用以下语句创建数据库分区组和表空间。可以将这些语句复制到一个名为 STORAGE.ddl 的文件中,然后使用下面的命令执行它们:

db2 -tvf STORAGE.ddl -z storage.log
清单 11. 创建数据库分区组和表空间的语句
CONNECT TO DSS_DB;

--------------------------------------------------
-- DDL Statements for DATABASE PARTITION GROUPS --
--------------------------------------------------
 
CREATE DATABASE PARTITION GROUP "FACT_GROUP" ON DBPARTITIONNUMS 
		(0,
		 1,
		 2,
		 3);

CREATE DATABASE PARTITION GROUP "DATE_GROUP" ON DBPARTITIONNUMS 
		(1);

CREATE DATABASE PARTITION GROUP "PRODUCT_GROUP" ON DBPARTITIONNUMS 
		(2);

CREATE DATABASE PARTITION GROUP "STORE_GROUP" ON DBPARTITIONNUMS 
		(3);

COMMIT WORK;

------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------

CREATE REGULAR TABLESPACE FACT_SMS IN DATABASE PARTITION GROUP FACT_GROUP
    PAGESIZE 4096 MANAGED BY SYSTEM 
	 USING ('d:\database\fact_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_tbsp3') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE DATE_SMS IN DATABASE PARTITION GROUP
DATE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\date_group') ON DBPARTITIONNUMS (1)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE PRODUCT_SMS IN DATABASE PARTITION GROUP
PRODUCT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\product_group') ON DBPARTITIONNUMS (2)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE STORE_SMS IN DATABASE PARTITION GROUP
STORE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:\database\store_group') ON DBPARTITIONNUMS (3)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

COMMIT WORK;

-- Mimic tablespace

ALTER TABLESPACE SYSCATSPACE
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE TEMPSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE USERSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;

COMMIT WORK;

------------------------------------------------
-- Update the bufferpool to use 1000 4K pages --
------------------------------------------------

ALTER BUFFERPOOL IBMDEFAULTBP SIZE 1000;

COMMIT WORK;
CONNECT RESET;

注意:表空间被定义为 "NO FILE SYSTEM CACHING",以避免文件系统缓存歪曲测试各种方法时得到的评测结果。

使用 db2batch 工具评测性能

db2batch 程序用于运行 清单 2 中的主查询。为了使用 db2batch 命令运行该查询,需要将查询保存在一个以分号结尾的文件中,并使用以下选项,使 db2batch 工具查看计时情况:

清单 12. 使用 db2batch 评测查询的性能
db2batch -d <dbname> -f <input_file> 
-i <elapsed_time> -iso <isolation level>
-o p <perf_detail> o <optlevel> r <rows_out> 
-r <result_file>

其中 <dbname> 是数据库名称,<input_file> 是以分号结尾、包含查询的文件。

  • -iso <isolation level>:
    在我们的测试中,默认隔离级别是 CS,但是默认情况下 db2batch 工具使用隔离级别 RR。如果使用隔离级别 RR 执行一个查询,那么使用隔离级别 CS 创建的 MQT 不会被考虑。为了解决这个问题,可以在 db2batch 命令中使用 -iso 选项和隔离级别 CS,以便查询选择 MQT。而且,应用程序可使用默认的 CS 隔离级别,不带 -iso 选项运行 db2batch 会导致它使用 RR 隔离级别,并可能导致锁争用。
  • -o - options options:
    • p <perf_detail>: 性能详细信息。返回数据库管理器、数据库、应用程序和语句的快照(只有在自动提交关闭,且处理的是单个语句,而非语句块时,才返回语句快照)。另外还返回缓冲池、表空间和 FCM的快照(只有在多数据库分区环境中才会返回 FCM 快照)。 对于例子 p 5,我们使用最详细的输出,但是也可以使用不同级别的性能输出。
    • o <optlevel>: 查询优化级别。(本文使用优化级别 5,这里不需要显式地指定这个优化级别,因为它是数据库的默认优化级别,如 清单 10 所示。)
    • r <rows_out>: 所获取且将发送到输出的行数。我们的例子 r 0 不发送行。
  • -r <result_file>: 结果文件。在我们的例子中,results.txt 是输出文件名,db2batch 将结果输出到该文件中。

在本文中,我们使用:

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r <output filename>

提高查询性能的方法

在本节中,让我们逐步了解用于提高 清单 2 中描述的查询的性能的各种不同方法。在讨论任何方法之前,必须创建基本的事实表和维度表。

步骤 A:创建好表空间之后,就要创建事实表和维度表。可以将 SKAPOOR 改为符合您自己环境的模式名。这样做时,务必更新 清单 2 中的查询,以反映适当的模式名。可以将下面的语句复制到一个名为 TEST1.ddl 的文件中,然后使用以下命令来执行该文件:

db2 -tvf TEST1.ddl -z test1.log
清单 13. TEST1.ddl 的内容
CONNECT TO DSS_DB;

---------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT"
---------------------------------------------------
 

CREATE TABLE "SKAPOOR "."SALES_FACT"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )   
		 DISTRIBUTE BY HASH("DATE_ID")   
		   IN "FACT_SMS" ;

-------------------------------------------------
-- DDL Statements for table "SKAPOOR "."DATE_DIM"
-------------------------------------------------
 

CREATE TABLE "SKAPOOR "."DATE_DIM"  (
		  "DATE_ID" DATE NOT NULL , 
		  "MONTH" INTEGER , 
		  "QUARTER" INTEGER , 
		  "YEAR" INTEGER )   
		 IN "DATE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."DATE_DIM"
-- DATE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."DATE_DIM" 
	ADD PRIMARY KEY
		("DATE_ID");



----------------------------------------------------
-- DDL Statements for table "SKAPOOR "."PRODUCT_DIM"
----------------------------------------------------
 

CREATE TABLE "SKAPOOR "."PRODUCT_DIM"  (
		  "PRODUCT_ID" INTEGER NOT NULL , 
		  "PRODUCT_DESC" CHAR(20) , 
		  "MODEL" CHAR(10) , 
		  "MAKE" CHAR(10) )   
		 IN "PRODUCT_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."PRODUCT_DIM"
-- PRODUCT_ID is the unique identifier
ALTER TABLE "SKAPOOR "."PRODUCT_DIM" 
	ADD PRIMARY KEY
		("PRODUCT_ID");



--------------------------------------------------
-- DDL Statements for table "SKAPOOR "."STORE_DIM"
--------------------------------------------------
 

CREATE TABLE "SKAPOOR "."STORE_DIM"  (
		  "STORE_ID" INTEGER NOT NULL , 
		  "LOCATION" CHAR(15) , 
		  "DISTRICT" CHAR(15) , 
		  "REGION" CHAR(15) )   
		 IN "STORE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."STORE_DIM"
-- STORE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."STORE_DIM" 
	ADD PRIMARY KEY
		("STORE_ID");


COMMIT WORK;

CONNECT RESET;

步骤 B:创建好表后,将数据插入到三个维度表中,并根据您自己的环境调整模式:

清单 14. 填充 DATE_DIM 表
db2 -td@ -vf date_insert.txt -z date_insert.log
清单 15. 填充 PRODUCT_DIM 表
db2 -td@ -vf product_insert.txt -z product_insert.log
清单 16. 填充 STORE_DIM 表
db2 -td@ -vf store_insert.txt -z store_insert.log

这三个文件的内容是:

DATE_DIM 表被填入 2006 年所有 365 天的值。

清单 17. date_insert.txt 的内容
connect to dss_db@

begin atomic
  declare cnt INT default 1;
  declare dat DATE default '01/01/2006';
  declare yer INT default 2006;
  declare quart INT default 1;

while (cnt <= 365) do    
    if (int(dat + cnt DAYS)/100) between 200601 and 200603 then
           set quart=1;
    elseif (int(dat + cnt DAYS)/100) between 200604 and 200606 then
           set quart=2;
    elseif (int(dat + cnt DAYS)/100) between 200607 and 200609 then
           set quart=3;
    elseif (int(dat + cnt DAYS)/100) between 200610 and 200612 then
           set quart=4;    
    end if;
    
    insert into SKAPOOR.DATE_DIM values (
	dat + cnt DAYS,
	(int(dat + cnt DAYS)/100) - 200600,
	quart,
	yer
    );
       
    set cnt=cnt+1;
end while;

end@

connect reset@

PRODUCT_DIM 表被填入 60,000 种产品。

清单 18. product_insert.txt 的内容
connect to dss_db@

drop sequence seq1@
drop sequence seq2@

create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@

begin atomic
   declare cnt INT default 1;

   while (cnt < 60001) do
	insert into SKAPOOR.PRODUCT_DIM values (
	    nextval for SEQ2,
	    'product desc' concat char(nextval for SEQ1),
            'model ' concat char(integer(rand()*1000)),
	    'maker ' concat char(integer(rand()*500))
	);
 	set cnt=cnt+1;
    	end while;
end@

drop sequence seq1@
drop sequence seq2@

connect reset@

STORE_DIM 表被填入 201 家商店。

清单 19. store_insert.txt 的内容
connect to dss_db@

drop sequence seq2@

create sequence seq2 as integer start with 0 increment by 1@

begin atomic
    declare cnt INT default 1;

    while (cnt < 202) do
       insert into SKAPOOR.STORE_DIM values (
	    nextval for SEQ2,
	    'location' concat char(integer(rand()*500)),
	    'district' concat char(integer(rand()*10)),
	    'region' concat char(integer(rand()*5))
       );
       set cnt=cnt+1;

    end while;
end@

drop sequence seq2@

connect reset@

步骤 C:将数据插入到 SALES_FACT 表中。根据您自己的环境调整模式。在我们的测试环境中,将数据插入到事实表花了约一个半小时的时间。

清单 20. 填充 SALES_FACT 表
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log
清单 21. sales_fact_insert.ddl 的内容
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

begin atomic

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
         (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
          select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
          INT(RAND()*200 + 1), RESERVE, U_ID + 1
          from   v
          where  U_ID < 60000)
     select date_id, product_id, store_id, quantity, price, transaction_details from v;

     set cnt1 = cnt1 + 1;
     set cnt  = cnt + 1;
   end while;

end@

VALUES (CURRENT TIMESTAMP)@

connect reset@

注意:清单 21 中,SALES_FACT 表是在一次事务处理中填充的,这需要大量的磁盘空间来作日志记录。为了降低日志记录的影响,可以创建一个存储过程,并分步提交插入内容:

清单 22. 填充 SALES_FACT 表的另一种方法
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging

create procedure salesFactPopulate()
specific salesFactPopulate
language sql

begin

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    
    
    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
    (
       values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
      union all
       select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
              INT(RAND()*200 + 1), RESERVE, U_ID + 1
         from v
        where U_ID < 60000
    )
    select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;

    commit work;

    set cnt1 = cnt1 + 1;
    set cnt=cnt+1;

   end while;

end@

-- populate the SALES_FACT table
invoke salesFactPopulate@

VALUES (CURRENT TIMESTAMP)@

connect reset@

步骤 D:为了理解各种不同的方法对所选查询访问计划的有怎样的影响,我们需要解释(Explain)查询,以查看 DB2 查询优化器选择的访问计划。为此,可使用 EXPLAIN 工具,这要求存在 EXPLAIN 表。为了创建 EXPLAIN 表,执行以下步骤:

  1. 进入 sqllib\misc 目录所在的位置。
    在我们的测试环境中,这个位置为 "C:\Program Files\IBM\SQLLIB\MISC"。
  2. 执行 db2 connect to dss_db
  3. 执行 db2 -tvf EXPLAIN .DDL

方法 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_DIMDATE_IDPK
PRODUCT_DIMPRODUCT_IDPK
STORE_DIMSTORE_IDPK
SALES_FACTDATE_IDFKDATE_DIM (DATE_ID)
SALES_FACTPRODUCT_IDFKPRODUCT_DIM (PRODUCT_ID)
SALES_FACTSTORE_IDFKSTORE_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)

练习:

  1. 在步骤 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 大小,计算方法如下:

  1. 请参阅 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
  2. 下面的查询计算平均每单元行数(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.
  3. 为了计算每个单元的间距,我们使用 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

    因此,EXTENTSIZE12

步骤 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:\database\fact_tbsp_mdc_EX120') ON DBPARTITIONNUMS (0)
USING ('d:\database\fact_tbsp_mdc_EX121') ON DBPARTITIONNUMS (1)
USING ('d:\database\fact_tbsp_mdc_EX122') ON DBPARTITIONNUMS (2)
USING ('d:\database\fact_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:\database\fact_tpart_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:\database\fact_tpart_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:\database\fact_tpart_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:\database\fact_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. 参照完整性约束清单 2115.00
2. 重复的 MQT清单 2103.42
3. 多维集群和重复的 MQT清单 3938.36
4. 表分区和重复的 MQT清单 42197.74
5. 表分区、MDC 和重复的 MQT清单 4532.21
6. 使用 MQT 预先计算聚合结果清单 27.61

结果表明,使用 MQT 预先计算聚合结果可以提高查询性能的效果最为显著。与 方法 1 中基本的星型模式布局相比,多维集群,以及表分区与 MDC 的组合,也可以显著提高查询性能。

在我们的环境中,重复的维度表可以略微提高性能。这是因为所有 4 个数据库分区都是逻辑分区,是在同一台物理机器上创建的。如果为数据库分区使用多台物理机器,那么这种方法应该可以显著提高性能,尤其是当数据库分区之间需要大量传送数据时,这种方法的效果尤为明显。

表分区本身实际上会使性能变得更糟。我们的测试中未创建任何索引来比较分区消除。事实表上的附加谓词进一步过滤向外连接每个分支中访问的分区。在表上创建一个或多个索引的另一个优点是可以取得更好的性能。这是 方法 4 中留给读者的一个练习。

这些测试表明,使用 DB2 9 中的各种特性可以提高 BI 查询的性能。


结束语

本文中讨论的这些方法只是提高 BI 环境中查询性能的一部分方法。请动手完成下一小节及本文各处所提供的练习。


练习

尝试一下:

  1. 可以使用 DB2 Design Advisor 获得对索引、多维集群、数据库分区和物化查询表的建议。使用 Design Advisor 查看对于 清单 2 中使用基本 SALES_FACT 表的查询,在索引、MDC 和 MQT 方面有什么建议。
  2. 在查询的示例工作负载上试验本文中讨论的一些方法。
  3. 在查询的一个工作负载上重复练习 1。

下载

描述名字大小
JOIN_ELIMjoin_elim.txt49KB
Test 1test1.txt117KB
Test 2test2.txt120KB
Test 3test3.txt117KB
Test 6test6.txt50KB
TPARTtpart.txt102KB
TPART_MDCtpart_mdc.txt118KB

参考资料

学习

获得产品和技术

  • 下载 IBM 产品评测版,获得来自 DB2®、Lotus®、Rational®、Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。
  • 用可从 developerWorks 直接下载的 IBM 试用软件 构建您的下一个开发项目。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=216032
ArticleTitle=提高商业智能环境中 DB2 查询的性能
publish-date=04262007