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

Advertisement

高效地运行大型查询,是商业智能环境中的顶级性能挑战。学习在这种环境中提高 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_FACT FACT TABLE DATE_ID DATE 产品售出日期
PRODUCT_ID INT 所购买产品的标识符
STORE_ID INT 出售产品的商店的标识符
QUANTITY INT 这次交易中售出产品的数量
PRICE INT 产品购买价格。[为了简单起见,该字段为整型,但是使用小数型更符合实际]
TRANSACTION_DETAILS CHAR(100) 关于此次交易的描述/详细信息
DATE_DIM DIMENSION TABLE DATE_ID NOT NULL DATE 惟一标识符
MONTH INT 日期记录所属的月份
QUARTER INT 日期记录所属的季度(第 1、第 2、第 3 或第 4 季度)
YEAR INT 日期记录所属的年份
PRODUCT_DIM DIMENSION TABLE PRODUCT_ID NOT NULL INT 产品惟一标识符
PRODUCT_DESC CHAR(20) 对产品的描述
MODEL CHAR(200) 产品型号
MAKE CHAR(50) 产品的质地
STORE_DIM DIMENSION TABLE STORE_ID NOT NULL INT 商店惟一标识符
LOCATION CHAR(15) 商店位置
DISTRICT CHAR(15) 商店所属街区
REGION CHAR(15) 商店所属区域

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

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

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

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

数据库分区信息

数据库分区组名 数据库分区数
FACT_GROUP 0,1,2,3
DATE_GROUP 1
PRODUCT_GROUP 2
STORE_GROUP 3

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

表空间信息

表空间名 数据库分区组
FACT_SMS FACT_GROUP SALES_FACT
DATE_SMS DATE_GROUP DATE_DIM
PRODUCT_SMS PRODUCT_GROUP PRODUCT_DIM
STORE_SMS STORE_GROUP STORE_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~1IBMSQLLIB" 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 个逻辑数据分区。在 etcservices 文件中,应确保有足够的端口用于创建 4 个数据分区。在我们的测试环境中,文件 C:WINDOWSsystem32driversetcservices 中包含关于实例 "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:databasefact_tbsp0') ON DBPARTITIONNUMS (0)       USING ('d:databasefact_tbsp1') ON DBPARTITIONNUMS (1)       USING ('d:databasefact_tbsp2') ON DBPARTITIONNUMS (2)       USING ('d:databasefact_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:databasedate_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:databaseproduct_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:databasestore_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>  |-------10--------20--------30--------40--------50--------60--------70--------80--------9|  |-------- XML error:  The previous line is longer than the max of 90 characters ---------|               -o p <perf_detail> o <optlevel> r <rows_out> -r <result_file>   |-------10--------20--------30--------40--------50--------60--------70--------80--------9|  |-------- XML error:  The previous line is longer than the max of 90 characters ---------|

其中 <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 [email protected] -vf date_insert.txt -z date_insert.log

清单 15. 填充 PRODUCT_DIM 表

                  db2 [email protected] -vf product_insert.txt -z product_insert.log

清单 16. 填充 STORE_DIM 表

                  db2 [email protected] -vf store_insert.txt -z store_insert.log

这三个文件的内容是:

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

清单 17. date_insert.txt 的内容

                  connect to [email protected]    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;    [email protected]    connect [email protected]

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

清单 18. product_insert.txt 的内容

                  connect to [email protected]    drop sequence [email protected]  drop sequence [email protected]    create sequence seq1 as integer start with 1 increment by [email protected]  create sequence seq2 as integer start with 1 increment by [email protected]    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;  [email protected]    drop sequence [email protected]  drop sequence [email protected]    connect [email protected]

STORE_DIM 表被填入 201 家商店。

清单 19. store_insert.txt 的内容

                  connect to [email protected]    drop sequence [email protected]    create sequence seq2 as integer start with 0 increment by [email protected]    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;  [email protected]    drop sequence [email protected]    connect [email protected]

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

清单 20. 填充 SALES_FACT 表

                  db2 [email protected] -vf sales_fact_insert.ddl -z sales_fact_insert.log

清单 21. sales_fact_insert.ddl 的内容

                  connect to [email protected]    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;    [email protected]    VALUES (CURRENT TIMESTAMP)@    connect [email protected]

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

清单 22. 填充 SALES_FACT 表的另一种方法

                  connect to [email protected]    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;    [email protected]    -- populate the SALES_FACT table  invoke [email protected]    VALUES (CURRENT TIMESTAMP)@    connect [email protected]

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

  1. 进入 sqllibmisc 目录所在的位置。
    在我们的测试环境中,这个位置为 "C:Program FilesIBMSQLLIBMISC"。
  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_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     |-------10--------20--------30--------40--------50--------60--------70--------80--------9|  |-------- XML error:  The previous line is longer than the max of 90 characters ---------|

文件 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  |--10--------20--------30--------40--------50--------60--------70--------80--------9|  |-------- XML error:  The previous line is longer than the max of 90 characters ---------|
    

    在 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: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 表由全年的事务组成,而我们的查询是比较各个月份的销售量,因此每个范围由一个月的数据组成。

为了演示分区

Similar Posts:

  • 数据仓库中拉动查询性能的三驾马车

    From : http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1303wulei/ 前言 在数据仓库领域中,无论是在生产系统中,还是 POC(Proof Of Concept) 性能测试,查询性能对于客户来说都是非常重要的性能指标.良好的查询性能 为各类数据仓库应用的高效作业奠定了基础.而对于查询性能来说,众所周知, 其主要性能瓶颈来自于系统 I/O,因此本文从数据仓库物理设计的角度出发, 阐述了影响查询性能的三项

  • ASP.net网站性能提高办法 无意中从网上浏览到了一篇提高网站性能的帖子转来学习下。

    ASP.net网站性能提高办法 无意中从网上浏览到了一篇提高网站性能的帖子转来学习下. 文章记录了26种优化网站性能的方法. 1. 数据库访问性能优化 数据库的连接和关闭 访问数据库资源需要创建连接.打开连接和关闭连接几个操作.这些过程需要多次与数据库交换信息以通过身份验证, 比较耗费服务器资源.ASP.NET中提供了连接池(Connection Pool)改善打开和关闭数据库对性能的影响.系统将用户的数据库连接放在连接池中, 需要时取出,关闭时收回连接,等待下一次的连接请求.连接池的大小是有限

  • DB2 Spatial Extender 性能调优(6)

    出处:51CTO.com整理 减少对空间函数的调用 当看到我们在本文前面使用的空间查询时,您会注意到,为了重叠测试,一个新的几何图形被构造成参数.现在,ST_LineString 构造函数是一个没有任何副作用的确定性的函数.DB2 优化器知道那些条件,它可以断定多次调用那个函数不会有害.取决于您的系统和查询,这可能是一个聪明的选择,但是也可能不是最佳的选择.例如,在使用 DPF 特性的分区环境中,在每个分区上构造 linestring,与在单独一个分区上构造几何图形,然后通过表队列将它分布到其他

  • 在Intranet环境中保护.NETWeb应用程序方案(转)

    Microsoft Corporation 内容简介 基于 Intranet Web 应用程序的安全性并不是不重要,因为它存在于许多控制网络中,并且对一个限制集合中的用户是可以访问的.不同个体和部门可能需要对应用程序提供的功能和数据有不同的访问等级,所以在传输过程中仍然必须保护机密数据的安全性.为了使问题复杂化,应用程序的安全性结构必须补偿任何安全性相关的问题,这些问题源于存在的基础和要配置应用程序的 Intranet 的操作特点. 通过关注某些常用分布式应用程序结构的要求,本章介绍了基于 In

  • [参考+理解]hibernate的查询和性能调优

    HQL hql语言,不需要记了,直接用nativeSql Nativesql>HQL>Ejbql>qbc>qbe 性能优化 1.session.clear 比如,你在同一个session中 一下先取出1000条数据,然后处理,在取1000条数据 处理,其实更前面的session缓存没用了,反而会造成内存问题,所以即使session.clear 2.1+N问题 /** * 描述1+N问题 category 和topic是一对多 * 在取出topic的时候,由于ManyToOne默认的

  • 桌面虚拟化场景中对存储系统IO性能的评估方法

    虚拟桌面系统依赖存储基础架构来承载用户环境和操作系统的不同部分.如果没有合适的存储系统的设计,用户的虚拟桌面会变得越来越慢,然后直到不可用,因为存储变为了最大的瓶颈.为了恰当的设计存储基础设施,除了存储容量外,我们还需要能够计算虚拟桌面系统需要的以及存储系统能够提供的每秒Input/Output Operations (也就是俗称的IOPS).计算和评估IOPS 需要从以下几方面考虑: (1)磁盘IOPS的理论推算 磁盘是整个存储系统的最基本组成单元,它完成一个I/O请求所花费的时间是由寻道时间

  • ZooKeeper学习第五期--ZooKeeper管理分布式环境中的数据

    引言 本节本来是要介绍ZooKeeper的实现原理,但是ZooKeeper的原理比较复杂,它涉及到了paxos算法.Zab协议.通信协议等相关知识,理解起来比较抽象所以还需要借助一些应用场景,来帮我们理解.由于内容比较多,一口气吃不成胖子,得慢慢来一步一个脚印,因此我对后期ZooKeeper的学习规划如下: 第一阶段: |---理解ZooKeeper的应用 |---ZooKeeper是什么 |---ZooKeeper能干什么 |---ZooKeeper 怎么使用 第二阶段: |---理解ZooK

  • 【转载】如何在FPGA设计环境中添加加时序约束

    转自:http://bbs.ednchina.com/BLOG_ARTICLE_198929.HTM 如何在FPGA设计环境中加时序约束 在给FPGA做逻辑综合和布局布线时,需要在工具中设定时序的约束.通常,在FPGA设计工具中都FPGA中包含有4种路径:从输入端口到寄存器,从寄存器到寄存器,从寄存器到输出,从输入到输出的纯组合逻辑. 通常,需要对这几种路径分别进行约束,以便使设计工具能够得到最优化的结果.下面对这几种路径分别进行讨论. 从输入端口到寄存器: 这种路径的约束是为了让FPGA设计工

  • 通过force index了解的MySQL查询的性能优化[经典实用]

    查询是数据库技术中最常用的操作.查询操作的过程比较简单,首先从客户端发出查询的SQL语句,数据库 服务端在接收到由客户端发来的SQL语句后, 执行这条SQL语句,然后将查询到的结果返回给客户端.虽然过程很简单,但不同的查询方式和数据库设置,对查询的性能将会有很在的影响. 因此,本文就在MySQL中常用的查询优化技术进行讨论.讨论的内容如:通过查询缓冲提高查询速度:MySQL对查询的自动优化:基于索引的排序: 不可达查询的检测和使用各种查询选择来提高性能. 一. 通过查询缓冲提高查询速度 一般我们

  • 5.2改进 ASP 应用程序中的字符串处理性能

    James Musson Developer Services, Microsoft UK 2003年3月 适用于: Microsoft? Active Server Pages? Microsoft Visual Basic?摘要:大多数 Active Server Pages (ASP) 应用程序都要通过字符串连接来创建呈现给用户的 HTML 格式的数据.本文对几种创建此 HTML 数据流的方法进行了比较,在特定情况下,某些方法在性能方面要优于其他方法.本文假定您已经具备一定的 ASP 和

Tags: