进一步理解 DB2 中的列组统计信息

利用 DB2 9.5 中多列统计信息的扩展用途改善基数估计值

利用 IBM® DB2® for Linux®, UNIX®, and Windows® (DB2) 中的多列统计信息,优化器可以在多个谓词相关联时确定一个更好的查询访问计划并提高查询性能。在本文中,学习如何使用多列统计信息以利用 DB2 9.5 优化器的增强功能,从而将多列统计信息的用途扩展到更多谓词中。

Samir Kapoor, DB2 UDB 高级支持分析师, IBM

Samir Kapoor 的照片Samir Kapoor 是一位 IBM 认证的 DB2 UDB 高级技术专家。Samir 目前在 DB2 UDB Advanced Support - Down system division(DSD)团队工作,他具有引擎领域的深入知识。



Vincent Corvinelli, DB2 优化器开发人员, IBM

Vincent Corvinelli 是 IBM Toronto Lab 的 DB2 Query Optimizer Development 团队的一名顾问软件开发人员。



2008 年 12 月 08 日

简介

理解 DB2 中的列组统计信息”(developerWorks,2006 年 12 月)一文描述了收集列组统计信息的重要性,并介绍了 DB2 SQL 优化器(后文简称为优化器)如何使用这些多列统计信息来检测两个或多个本地或连接等式谓词之间的统计关联。在 DB2 9.5 中,优化器进一步将多列统计信息的使用扩展到了更广泛的谓词范围中。

优化器凭借精确的基数估计值来准确计算出每一个待定查询访问计划的成本。基数估计是这样一种过程:在应用了谓词或执行了聚集之后,优化器使用统计信息确定部分查询结果的大小。对于访问计划的每个操作符,优化器将估计该操作符的基数输出。应用一个或更多谓词可以减少输出流基数。

在计算谓词对于基数估计值的组合过滤效果时,通常会假设这些谓词彼此之间是独立的。但在统计方面,这些谓词可以彼此关联。单独地处理多个谓词通常会导致优化器低估基数值。而基数值的低估又会导致优化器选择一个次优的访问计划。

优化器会考虑使用多列统计信息来检测统计关联,并更加准确地估计多个谓词组合的过滤效果。本文描述了优化器如何利用多列统计信息来检测统计关联,并更加准确地估算多个等式谓词对于应用了至少两个本地 IN、OR 和等式谓词的 SQL 语句的组合过滤效果,和它们对于应用了某种等级的 OR 谓词的 SQL 语句的过滤效果。“理解 DB2 中的列组统计信息” 一文描述了优化器如何利用多列统计信息来检测两个或多个本地等式谓词间的关联,以及在一对表中至少使用了两个等式连接谓词的两个或多个表的连接关联。RUNSTATS 命令选项在本文中的使用方式与在上篇 文章 中所描述的使用方式相同,所以本文中不再加以描述。


多个本地等式和本地 IN 谓词的统计关联

如果 SQL 语句的 WHERE 子句使用了多个谓词:

    	        C1=? AND C2 IN ( ?, ?, ? )

并且收集了 (C1, C2) 的多列统计信息的话,那么优化器就会试着检测这些谓词间的统计关联以提高基数估计值。如下谓词除外:

  • 带有 IN 或 OR 操作符的连接谓词
  • 带有不等式、LIKE 或 IS NULL 操作符的本地谓词
  • 带有子查询的谓词

C1=? 谓词就是本地等式谓词的一个例子,一个本地等式谓词是一个应用于单个表的等式谓词,其描述如下:

	        COLUMN = literal

其中 literal 可以是以下任一内容:

  • 一个常量值
  • 一个参数标记或一个主变量
  • 一个专用寄存器(例如,CURRENT DATE)

C2 IN ( ?, ?, ? ) 谓词则是本地 IN 谓词的一个例子,一个本地 IN 谓词是一个应用于同一个表格 —— 与本地谓词所应用的表格相同 —— 的等式谓词,其描述如下:

          COLUMN IN ( <VALUE LIST> )

其中 <VALUE LIST> 是一个以逗号隔开的一个或多个上述(在本地等式谓词中)literal 的列表。

一个相当于 IN 谓词的 OR 谓词可以代替 IN 谓词在 SQL 语句中指定,而且优化器将会在说明统计关联时按相同的方式处理之,也就是说:

    	    COL IN  ( literal_1, literal_2, ..., literal_n )

相当于

          COL=literal_1 OR COL=literal_2 OR ... OR COL=literal_n

下面的例子演示了优化器为其检测本地 IN、OR 和等式谓词间的关联:

a) COL_1 IN ( <VALUE LIST> ) AND COL_2=literal AND COL_3=literal
b) (COL_1=literal_1 OR COL_1=literal_2 OR ... OR COL_1=liternal_n) AND COL_2=literal AND ... AND COL_m=literal
c) COL_1 IN ( <VALUE LIST> ) AND COL_2 IN ( <VALUE LIST> ) AND ... AND COL_m IN ( <VALUE LIST> )
d) (COL_1=literal_1 OR COL_1=literal_2) AND (COL_2=literal_1 OR COL_2=literal_2) AND ... AND (COL_m=literal_1 OR COL_M=literal_2)
e) COL_1 IN ( <VALUE LIST> ) AND ... And COL_m IN ( <VALUE LIST> ) AND COL_1_2=literal AND ... AND COL_1_k=literal
f) (COL_1=literal_1 OR COL_1=literal_2) AND COL_2=literal AND COL_3=literal
g) (C)L_1=literal_1 OR COL_1=literal_2) AND (COL_2=literal_1 OR COL_2=literal_2) AND COL_3=literal

下面这些谓词是优化器不会考虑为其检测统计关联的谓词的例子:

a) (COL_1=literal AND COL_2=literal) OR (COL_1=literal AND COL_2=literal AND COL_3=literal)
b)((COL_1=literal AND COL_2=literal) OR (COL_1=literal AND COL_2=literal)) AND COL_3=literal
c)( COL_1 IN ( <VALUE LIST> ) OR (COL_2 IN ( <VALUE LIST> ) ) AND COL_3=literal

示例 1:C1 IN ( <VALUE LIST> ) AND C2 = literal

注意:请将本文所有示例中的 SKAPOOR 替换为您自己的模式。

这些示例是在如下所示的环境中测试的,使用的是 SAMPLE 数据库,SAMPLE 数据库可以通过执行 db2sampl 来创建:

清单 1. 样例的测试环境
      DB21085I  Instance "skapoor" uses "64" bits and DB2 code release "SQL09051"
      with level identifier "03020107".
      Informational tokens are "DB2 v9.5.0.1", "s080328", "U814639", and Fix Pack"1".
      Product is installed at "/home2/skapoor/sqllib".

      Configuration:  (as displayed by the db2exfmt tool)

        Database Context:
        ----------------
             Parallelism:            None
             CPU Speed:              4.000000e-05
             Comm Speed:             100
             Buffer Pool size:       1000
             Sort Heap size:         256
             Database Heap size:     1200
             Lock List size:         100
             Maximum Lock List:      10
             Average Applications:   1
             Locks Available:        640

        Package Context:
        ---------------
             SQL Type:               Dynamic
             Optimization Level:     5
             Blocking:               Block All Cursors
             Isolation Level:        Cursor Stability


        STMTHEAP: (Statement heap size)
                  6402

考虑对 SAMPLE 数据库的 EMPLOYEE 表执行如下查询:

清单 2. 对 SAMPLE 数据库的 EMPLOYEE 表执行的查询
      SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY 
        FROM EMPLOYEE 
       WHERE JOB IN ('CLERK', 'SALESREP') AND 
	           WORKDEPT = 'A00' 
      ORDER BY JOB, SALARY

该查询从 EMPLOYEE 表返回四条记录:

清单 3. 从 EMPLOYEE 表返回的记录
FIRSTNME     LASTNAME        JOB      WORKDEPT SALARY
------------ --------------- -------- -------- -----------
GREG         ORLANDO         CLERK    A00         39250.00
SEAN         O'CONNELL       CLERK    A00         49250.00
DIAN         HEMMINGER       SALESREP A00         46500.00
VINCENZO     LUCCHESSI       SALESREP A00         66500.00

4 record(s) selected.

EXPLAIN 工具可以用于查看优化器选择的查询访问计划,要使用该工具需要有 EXPLAIN 表。EXPLAIN 表可以通过执行以下代码来创建:

      db2 -tvf $DB2PATH/misc/EXPLAIN.DDL

SAMPLE 在创建最初,还没有在表上收集统计信息。要在 EMPLOYEE 表上收集统计信息,可以使用 RUNSTATS 工具。下面的 RUNSTATS 命令在每一个列上收集了统计信息,包括分布统计信息和所有在 EMPLOYEE 表中定义的索引上的详细统计信息(如果存在的话):

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
    WITH DISTRIBUTION AND DETAILED INDEXES ALL

创建了 EXPLAIN 表并收集了统计信息之后,SET CURRENT EXPLAIN MODE 语句就可以用于将一个或多个语句的查询访问计划插入到 EXPLAIN 表中了,如下所示:

清单 4. 将查询访问计划的详细信息插入到 EXPLAIN 表中
SET CURRENT EXPLAIN MODE EXPLAIN;

SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY 
  FROM EMPLOYEE 
  WHERE JOB IN ('CLERK', 'SALESREP') AND 
         WORKDEPT = 'A00' 
  ORDER BY JOB, SALARY;
     
SET CURRENT EXPLAIN MODE NO;

db2exfmt 工具读取 EXPLAIN 表中的数据,并将查询访问计划以文本文件的形式呈现:

db2exfmt -d SAMPLE -1 -g -o exfmt_example1.out

文件 exfmt_example1.out 包含了一个类似如下所示的访问计划,它的估计基数为 1:

清单 5. 查询访问计划
                  Rows
                 RETURN
                 (   1)
                  Cost
                   I/O
                   |
                 1.19048
                 TBSCAN
                 (   2)
                 10.7902
                    1
                   |
                 1.19048
                 SORT
                 (   3)
                 10.7387
                    1
                   |
                 1.19048
                 FETCH
                 (   4)
                 10.6299
                    1
                /---+---\
              5           42
           IXSCAN   TABLE: SKAPOOR
           (   5)      EMPLOYEE
           2.27828
              0
             |
             42
       INDEX: SKAPOOR
            XEMP2

基数估计值 1 与实际的结果 4 不符。优化器假定两个谓词是独立的,因为相关的索引或列组统计信息不存在。RUNSTATS 工具可以用于在组 (JOB,WORKDEPT) 上收集列组统计信息,以此为优化器提供适当的信息来检测两个列之间的统计关联(如果存在的话):

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE ON ALL COLUMNS 
	AND COLUMNS ((JOB,WORKDEPT)) WITH DISTRIBUTION 
	AND DETAILED INDEXES ALL

重复上面的步骤,再次解释查询,然后生成查询访问计划之后,优化器就会计算出一个更好的基数估计值,因为它在两个列上收集了列组统计信息:

清单 6. 具有更好的基数估计值的查询访问计划
                  Rows
                 RETURN
                 (   1)
                  Cost
                   I/O
                   |
                    5
                 TBSCAN
                 (   2)
                 10.8458
                    1
                   |
                    5
                 SORT
                 (   3)
                 10.7944
                    1
                   |
                    5
                 FETCH
                 (   4)
                 10.6299
                    1
                /---+---\
              5           42
           IXSCAN   TABLE: SKAPOOR
           (   5)      EMPLOYEE
           2.27828
              0
             |
             42
       INDEX: SKAPOOR
            XEMP2

由于列组统计信息是一个均匀分布的统计信息,所以基数估计值比实际值 4 稍高了一些。您可能已经注意到了,查询访问计划本身并未随着基数估计值的增大而改变。为了更好的说明如何提高基数估计值,本文中所描述的例子都很简单。如果语句涉及到更大的表格和两个或者更多个表的连接的话,查询访问计划就很可能会因为基数估计值的提高而改变。

示例 2:C1 IN ( <VALUE LIST> ) AND C2 IN ( <VALUE LIST> )

这个示例解释说明了在两个 IN 谓词上的列组统计信息的效果。考虑以下检索某一部门的经理和设计人员的奖金和薪水的查询:

清单 7. 奖金和薪水查询
      SELECT FIRSTNME, LASTNAME, WORKDEPT, JOB, BONUS, SALARY
        FROM EMPLOYEE
       WHERE WORKDEPT IN ('D11','D21') AND 
	           JOB IN ('MANAGER','DESIGNER') 
      ORDER BY WORKDEPT, SALARY

该查询从 EMPLOYEE 表返回 12 条记录:

清单 8. 从 EMPLOYEE 表返回的记录
    FIRSTNME     LASTNAME        WORKDEPT JOB      BONUS       SALARY
    ------------ --------------- -------- -------- ----------- -----------
    MASATOSHI    YOSHIMURA       D11      DESIGNER      500.00    44680.00
    JENNIFER     LUTZ            D11      DESIGNER      600.00    49840.00
    JAMES        WALKER          D11      DESIGNER      400.00    50450.00
    MARILYN      SCOUTTEN        D11      DESIGNER      500.00    51340.00
    BRUCE        ADAMSON         D11      DESIGNER      500.00    55280.00
    DAVID        BROWN           D11      DESIGNER      600.00    57740.00
    ELIZABETH    PIANKA          D11      DESIGNER      400.00    62250.00
    KIYOSHI      YAMAMOTO        D11      DESIGNER      500.00    64680.00
    WILLIAM      JONES           D11      DESIGNER      400.00    68270.00
    REBA         JOHN            D11      DESIGNER      600.00    69840.00
    IRVING       STERN           D11      MANAGER       500.00    72250.00
    EVA          PULASKI         D21      MANAGER       700.00    96170.00

      12 record(s) selected.

首先,在没有获得 (JOB,WORKDEPT) 列组统计信息的情况下检查访问查询计划和基数估计值。可以按如下方式在 EMPLOYEE 表上执行另外一个 RUNSTATS 命令来完成该检查:

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   WITH DISTRIBUTION AND DETAILED INDEXES ALL

由于前面收集的统计信息被最新的 RUNSTATS 命令清除了,所以先前收集的列组统计信息不复存在。您可以像在示例 1 中那样使用 EXPLAIN 和 db2exfmt 工具来生成查询访问计划,以此来检查优化器估计的基数:

清单 9. 将查询访问计划的详细信息插入到 EXPLAIN 表中
      SET CURRENT EXPLAIN MODE EXPLAIN;

      SELECT FIRSTNME, LASTNAME, WORKDEPT, JOB, BONUS, SALARY
        FROM EMPLOYEE
       WHERE WORKDEPT IN ('D11','D21') AND 
	           JOB IN ('MANAGER','DESIGNER') 
      ORDER BY WORKDEPT, SALARY
      
      SET CURRENT EXPLAIN MODE NO;


      db2exfmt -d SAMPLE -1 -g -o exfmt_example2.out

文件 exfmt_example2.out 一定会包含一个类似于如下所示的查询访问计划,它的估计基数为 7:

清单 10. 查询访问计划
                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
               7.28572
               TBSCAN
               (   2)
               13.7066
                  1
                 |
               7.28572
               SORT
               (   3)
               13.5723
                  1
                 |
               7.28572
               NLJOIN
               (   4)
               13.1318
                  1
           /------+------\
         2               3.64286
      TBSCAN             FETCH
      (   5)             (   6)
       0.006             11.0934
         0                  1
        |               /---+---\
         2            9           42
 TABFNC: SYSIBM    IXSCAN   TABLE: SKAPOOR
      GENROW       (   7)      EMPLOYEE
                   2.55364
                      0
                     |
                     42
               INDEX: SKAPOOR
                    XEMP2

在清单 9 所示的查询访问计划中,要注意表 EMPLOYEE 和一个表函数 GENROW 间的一个连接。当使用了一个 IN 谓词(或一个等价的 OR 谓词)时,优化器将会考虑使用一个 IN 到 JOIN 的变换,将一个 IN 谓词转换成一个连接谓词。GENROW 表函数会生成在 IN 谓词的 <VALUE LIST> 中所列的值。当以连接的形式使用 IN 谓词时,优化器将仍会考虑为它检测统计关联。

基数估计值 7 与实际结果 12 不符。和示例 1 一样,在列 (JOB,WORKDEPT) 上收集列组统计信息会在计算两个 IN 谓词的组合过滤效果时为优化器提供必要的信息,用以说明统计关联:

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   ON ALL COLUMNS AND COLUMNS ((JOB,WORKDEPT)) 
   WITH DISTRIBUTION AND DETAILED INDEXES ALL

重复上面的步骤,再次解释查询,生成查询访问计划之后,优化器会计算出一个更好的、与实际结果很接近的基数估计值:

清单 11. 具有更精确的基数估计值的查询访问计划
                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
                11.2
               TBSCAN
               (   2)
               13.9768
                  1
                 |
                11.2
               SORT
               (   3)
               13.8033
                  1
                 |
                11.2
               NLJOIN
               (   4)
               13.1318
                  1
           /------+------\
         2                 5.6
      TBSCAN             FETCH
      (   5)             (   6)
       0.006             11.0934
         0                  1
        |               /---+---\
         2            9           42
 TABFNC: SYSIBM    IXSCAN   TABLE: SKAPOOR
      GENROW       (   7)      EMPLOYEE
                   2.55364
                      0
                     |
                     42
               INDEX: SKAPOOR
                    XEMP2

示例 3:C1 IN ( <VALUE LIST> ) AND C2 IN ( <VALUE LIST> ) AND C3=literal

在这个例子中,将向示例 2 的查询中添加第三个谓词,确定有哪些职工得到了 $500 的奖金:

清单 12. 添加第三个谓词以查找 $500 的奖金
SELECT FIRSTNME, LASTNAME, WORKDEPT, JOB, BONUS, SALARY
  FROM EMPLOYEE
  WHERE WORKDEPT IN ('D11','D21') AND 
        JOB IN ('MANAGER','DESIGNER') AND
         BONUS = 500
  ORDER BY WORKDEPT, SALARY

该查询从 EMPLOYEE 表返回 5 条记录:

清单 13. 从 EMPLOYEE 表返回的记录
FIRSTNME     LASTNAME        WORKDEPT JOB      BONUS       SALARY
------------ --------------- -------- -------- ----------- -----------
MASATOSHI    YOSHIMURA       D11      DESIGNER      500.00    44680.00
MARILYN      SCOUTTEN        D11      DESIGNER      500.00    51340.00
BRUCE        ADAMSON         D11      DESIGNER      500.00    55280.00
KIYOSHI      YAMAMOTO        D11      DESIGNER      500.00    64680.00
IRVING       STERN           D11      MANAGER       500.00    72250.00

  5 record(s) selected.

如果您使用如下的代码:

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   WITH DISTRIBUTION AND DETAILED INDEXES ALL

在没有列组统计信息的情况下再次收集统计信息的话,优化器会选择一个类似如下所示的查询访问计划,它的基数估计值为 2:

清单 14. 查询访问计划
                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
               2.42857
               TBSCAN
               (   2)
               13.8494
                  1
                 |
               2.42857
               SORT
               (   3)
               13.7636
                  1
                 |
               2.42857
               NLJOIN
               (   4)
               13.5765
                  1
           /------+------\
         2               1.21429
      TBSCAN             FETCH
      (   5)             (   6)
       0.006             11.3158
         0                  1
        |               /---+---\
         2            9           42
 TABFNC: SYSIBM    IXSCAN   TABLE: SKAPOOR
      GENROW       (   7)      EMPLOYEE
                   2.55364
                      0
                     |
                     42
               INDEX: SKAPOOR
                    XEMP2

在 WHERE 子句中应用了三个谓词,如果假设它们是独立的话,会导致优化器低估基数。为了解释说明优化器如何使用索引统计信息以及列组统计信息来检测统计关联,要创建一个带有在谓词中所引用的三个列 (JOB,WORKDEPT,BONUS) 的索引并收集统计信息:

清单 15. 创建索引并收集统计信息
CREATE INDEX JOB_DEPT_BONUS ON EMPLOYEE (JOB,WORKDEPT,BONUS)
      
-- The RUNSTATS command provides the option to collect statistics on a set of
-- indexes only, without affecting the statistics previously collected.
RUNSTATS ON TABLE SKAPOOR.EMPLOYEE F
OR DETAILED INDEXES SKAPOOR.JOB_DEPT_BONUS

然后优化器会使用新创建的索引和在其上收集的统计信息来更正查询访问计划的基数估计值:

清单 16. 从查询访问计划中更正了的基数估计值
                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
                5.25
               TBSCAN
               (   2)
               13.5227
                  1
                 |
                5.25
               SORT
               (   3)
               13.4087
                  1
                 |
                5.25
               NLJOIN
               (   4)
               13.0875
                  1
           /------+------\
         2                2.625
      TBSCAN             FETCH
      (   5)             (   6)
       0.006             11.0713
         0                  1
        |               /---+---\
         2          2.625         42
 TABFNC: SYSIBM    IXSCAN   TABLE: SKAPOOR
      GENROW       (   7)      EMPLOYEE
                   2.85933
                      0
                     |
                     42
               INDEX: SKAPOOR
               JOB_DEPT_BONUS

例 4:C1=literal OR C1=literal2) AND (C2=literal OR C2=literal2) AND C3=literal

这个例子与示例 3 等效,它使用了等效的 OR 谓词来代替 IN 谓词:

清单 17. 等效 OR 谓词代替 IN 谓词
      SELECT FIRSTNME, LASTNAME, WORKDEPT, JOB, BONUS, SALARY
        FROM EMPLOYEE
       WHERE (WORKDEPT = 'D11' OR WORKDEPT = 'D21') AND 
	           (JOB = 'MANAGER' OR JOB = 'DESIGNER') AND
	           BONUS = 500
      ORDER BY WORKDEPT, SALARY

该查询返回的结果与示例 3 返回的结果相同。这个示例解释说明了部分统计信息对于优化器估计基数的能力的影响。撤销示例 3 中创建的索引,并只使用组((JOB,WORKDEPT))上的列组统计信息再次收集统计信息:

DROP INDEX JOB_DEPT_BONUS
RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   ON ALL COLUMNS AND COLUMNS 
     ((JOB,WORKDEPT)) 
   WITH DISTRIBUTION AND DETAILED INDEXES ALL

使用在合适的 IN、OR 和等式谓词所引用的列的子集上收集的列组统计信息,优化器估计出了一个更接近于真实结果的基数,但如果列组统计信息是在全部三个列上收集的话,该估计值的精确度要低于示例 3 中所示值:

清单 18. 查询访问计划
                Rows
               RETURN
               (   1)
                Cost
                 I/O
                 |
               3.73333
               TBSCAN
               (   2)
               13.9174
                  1
                 |
               3.73333
               SORT
               (   3)
               13.8186
                  1
                 |
               3.73333
               NLJOIN
               (   4)
               13.5765
                  1
           /------+------\
         2               1.86667
      TBSCAN             FETCH
      (   5)             (   6)
       0.006             11.3158
         0                  1
        |               /---+---\
         2            9           42
 TABFNC: SYSIBM    IXSCAN   TABLE: SKAPOOR
      GENROW       (   7)      EMPLOYEE
                   2.55364
                      0
                     |
                     42
               INDEX: SKAPOOR
                    XEMP2

优化器使用了在组 (JOB,WORKDEPT) 上收集的列组统计信息来说明两个 OR 谓词间的统计关联,但不包括列组中的 BONUS,它认为 BONUS=500 谓词独立于那两个 OR 谓词,结果导致稍稍低估了最终的基数。

注意:如果分析一下以上查询的 db2exfmt 输出的 Optimized Statement 部分的话,您会注意到 OR 谓词被转换成了与它们等效的 IN 谓词:

清单 19. 被转换成了与它们等效的 IN 谓词的 OR 谓词
Optimized Statement:
-------------------
SELECT Q5.FIRSTNME AS "FIRSTNME", Q5.LASTNAME AS "LASTNAME", Q5.WORKDEPT AS
        "WORKDEPT", Q5.JOB AS "JOB", +0000500.00 AS "BONUS", Q5.SALARY AS
        "SALARY"
FROM SKAPOOR.EMPLOYEE AS Q5
WHERE (Q5.BONUS = +0000500.00) AND Q5.JOB IN ('MANAGER ', 'DESIGNER') AND
        Q5.WORKDEPT IN ('D11', 'D21')
ORDER BY Q5.WORKDEPT, Q5.SALARY

在全部三列上收集列组统计信息会导致与示例 3 相同的基数估计值。在这种情况下,您仍然在前面的两列 (JOB,WORKDEPT) 上收集列组统计信息,并包括整个三列 (JOB,WORKDEPT,BONUS):

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   ON ALL COLUMNS AND COLUMNS
    ((JOB,WORKDEPT), (JOB,WORKDEPT,BONUS)) 
  WITH DISTRIBUTION AND DETAILED INDEXES ALL

正如在 “理解 DB2 中的列组统计信息” 中所描述的那样,您可以在相同的列集合间收集到一个或更多的列组统计信息。收集这些统计信息后生成的查询访问计划与示例 3 中的最后的计划相同。至于这种情况的验证就留给您作练习。

示例 5:索引 “或” 运算

这个例子解释说明了收集列组统计信息如何可以提高索引 “或” 运算查询计划的基数估计值。考虑如下对 EMPLOYEE 表检索所有 A00 部门的收银员和销售代表的查询:

清单 20. 对 EMPLOYEE 表检索所有 A00 部门的收银员和销售代表的查询
      SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
        FROM EMPLOYEE
       WHERE JOB IN ('CLERK', 'SALESREP') AND
             WORKDEPT='A00'
      ORDER BY JOB, SALARY

该检索从 EMPLOYEE 表返回 4 条记录:

清单 21. 查询返回四条记录
    FIRSTNME     LASTNAME        JOB      WORKDEPT SALARY
    ------------ --------------- -------- -------- -----------
    GREG         ORLANDO         CLERK    A00         39250.00
    SEAN         O'CONNELL       CLERK    A00         49250.00
    DIAN         HEMMINGER       SALESREP A00         46500.00
    VINCENZO     LUCCHESSI       SALESREP A00         66500.00

      4 record(s) selected.

为了更好地解释说明基数估计值的提高,撤销除主键索引外的所有 EMPLOYEE 表上的现有索引:

      DROP INDEX XEMP2

创建如下索引,该索引含有以上查询的 WHERE 子句的谓词引用的全部两个列,由 SALARY 列隔开:

 CREATE INDEX IND2 ON EMPLOYEE (JOB,SALARY,WORKDEPT)

在 EMPLOYEE 表和它的新的及剩余的索引上再次收集统计信息:

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
    WITH DISTRIBUTION AND DETAILED INDEXES ALL

在这个例子中,使用优化配置文件特性会迫使优化器选择一个索引 “或” 执行表访问操作。为此,优化配置文件需要遵循两个优化器原则:

  1. 一个原则是禁用 IN 谓词到连接谓词的转换
  2. 另一个原则是迫使优化器选择索引 “或” 操作来访问 EMPLOYEE 表

创建优化配置文件的第一步是创建一个称为 example5.xml 的 XML 文件,它包含如下所示的内容:

清单 22. XML 文件的内容
<?xml version="1.0" encoding="UTF-8"?>

<OPTPROFILE VERSION="9.5.1">
  <STMTPROFILE ID="Example 5 Index oring test">
    <STMTKEY>
    <![CDATA[SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
        FROM EMPLOYEE
       WHERE JOB IN ('CLERK', 'SALESREP') AND
             WORKDEPT='A00'
      ORDER BY JOB, SALARY]]>
   </STMTKEY>

    <OPTGUIDELINES>
      <INLIST2JOIN OPTION="DISABLE" TABLE="EMPLOYEE" COLUMN="JOB"/>
      <IXOR TABLE="EMPLOYEE" INDEX="IND2"/>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

第二步是创建一个称为 example5.del 的 del 文件,它包含了如下所示的内容:

 "SKAPOOR","IXORPLAN","example5.xml"

配置文件的模式为 SKAPOOR 的话,您关联到配置文件的名称就应该是 IXORPLAN,example5.xml 就是在第一步中创建的 XML 文件,它包含了描述该配置文件的内容。

第三步需要将 example5.xml 和 the example5.del 文件放置在相同的位置中并发出如下命令:

清单 23. 使用 example5.xml 和 example5.del 的命令
-- Create the OPT_PROFILE table, if it does not already exist
CREATE TABLE SYSTOOLS.OPT_PROFILE (
  SCHEMA VARCHAR(128) NOT NULL,
  NAME VARCHAR(128) NOT NULL,
  PROFILE BLOB (2M) NOT NULL,
    PRIMARY KEY ( SCHEMA, NAME ))
         
-- Add an entry to OPT_PROFILE table for our index-oring guideline
IMPORT FROM example5.del OF DEL 
   MODIFIED BY LOBSINFILE 
   INSERT INTO SYSTOOLS.OPT_PROFILE

要查看使用了创建的优化配置文件的查询访问计划,可以结合使用 SET CURRENT OPTIMIZATION PROFILE 语句和 SET CURRENT EXPLAIN MODE statement,如下所示:

清单 24. 查看使用了优化配置文件的查询访问计划
      -- use the IXORPLAN profile
      SET CURRENT OPTIMIZATION PROFILE="IXORPLAN"

      SET CURRENT EXPLAIN MODE EXPLAIN

      SELECT FIRSTNME, LASTNAME, JOB, WORKDEPT, SALARY
        FROM EMPLOYEE
       WHERE JOB IN ('CLERK', 'SALESREP') AND
             WORKDEPT='A00'
      ORDER BY JOB, SALARY

      SET CURRENT EXPLAIN MODE NO

优化器会选择类似如下所示的查询访问计划:

清单 25. 查询访问计划
                    Rows
                   RETURN
                   (   1)
                    Cost
                     I/O
                     |
                   1.19048
                   TBSCAN
                   (   2)
                   13.0404
                  0.963719
                     |
                   1.19048
                   SORT
                   (   3)
                   12.967
                  0.963719
                     |
                   1.19048
                   FETCH
                   (   4)
                   12.8278
                  0.963719
                  /---+---\
             1.19048        42
             RIDSCN   TABLE: SKAPOOR
             (   5)      EMPLOYEE
             4.89404
                0
          /-----+-----\
    0.952381         0.238095
     SORT             SORT
     (   6)           (   8)
     2.7352           2.21536
        0                0
       |                |
    0.952381         0.238095
     IXSCAN           IXSCAN
     (   7)           (   9)
     2.6272           2.10736
        0                0
       |                |
       42               42
 INDEX: SKAPOOR   INDEX: SKAPOOR
      IND2             IND2

如果所生成的查询访问计划并非上面所示的索引 “或” 计划的话,那么优化配置文件的设置会出现一个问题。如果优化器使用了该配置文件的话,您会在 db2exfmt 输出中看到如下信息:

    Profile Information:
    --------------------
    OPT_PROF: (Optimization Profile Name)
            SKAPOOR.IXORPLAN
    STMTPROF: (Statement Profile Name)
            Example 5 Index oring test

至于确定一个在列 (JOB,WORKDEPT) 上收集列组统计信息的合适方法,就留与读者作练习。收集了列组统计信息之后,查询访问计划就会显示出提高了的基数估计值:

清单 26. 查询访问计划中提高了的基数估计值
                    Rows
                   RETURN
                   (   1)
                    Cost
                     I/O
                     |
                      5
                   TBSCAN
                   (   2)
                   13.878
                      1
                     |
                      5
                   SORT
                   (   3)
                   13.7665
                      1
                     |
                      5
                   FETCH
                   (   4)
                   13.4746
                      1
                  /---+---\
                5           42
             RIDSCN   TABLE: SKAPOOR
             (   5)      EMPLOYEE
             4.89404
                0
          /-----+-----\
        4                1
     SORT             SORT
     (   6)           (   8)
     2.7352           2.21536
        0                0
       |                |
        4                1
     IXSCAN           IXSCAN
     (   7)           (   9)
     2.6272           2.10736
        0                0
       |                |
       42               42
 INDEX: SKAPOOR   INDEX: SKAPOOR
      IND2             IND2

对于每一个 IXSCAN 操作符,基数都被更正了,从而说明谓词间的关联:

  • JOB='CLERK' AND WORKDEPT='A00'
  • JOB='SALESREP' AND WORKDEPT='A00'

而且 RIDSCN 和 FETCH 操作符的基数也被更正了,这样就可以说明 IN 谓词和等式谓词间的统计关联。


OR 操作符的子项内的多个本地等式谓词的统计关联

如果一个 SQL 语句的 WHERE 子句应用了一个其每一个子项都带有多个本地谓词的 OR 操作符,如下所示:

	        (C1=literal_1 AND C2=literal_2) OR 
	        (C1=literal_3 AND C2=literal_4) OR 
	        (C1=literal_5 AND C2=literal_6)

并且在 (C1,C2) 上收集了多列统计信息的话,那么优化器将会试着检测谓词之间的统计关联,以提高 OR 谓词的过滤效果。在本文中,上述的 OR 操作符被描述为一个带有三个子项的单个 OR 操作符:

  1. (C1=literal_1 AND C2=literal_2)
  2. (C1=literal_3 AND C2=literal_4)
  3. (C1=literal_5 AND C2=literal_6)

但当 OR 操作符包含如下任一项的话,这种情况都不会发生:

  • 任一子项内的非本地等式谓词
  • 两个或多个子项内引用的不同列组

下面是优化器会为其检测本地 IN、OR 和等式谓词间的关联的一些例子:

          a) (COL_1=literal_1 AND COL_2=literal_2) OR 
             (COL_1=literal_3 AND COL_2=literal_4) OR 
                              ...                  OR 
             (COL_1=literal_n AND COL_2=literal_m)

下面是优化器不会考虑为其检测统计关联的谓词的例子:

          a) (COL_1=literal_1 AND COL_2=literal_2) OR 
             (COL_1=literal_3 AND COL_2=literal_4 AND COL_3=literal_5)
             
          b) (COL_1=literal_1 AND COL_2=literal_2) OR 
             (COL_1=literal_3 AND COL_2=literal_4) OR 
             (COL_1=literal_5 AND COL_2=literal_6 AND COL_3=literal_7)

示例 6:(C1=LITERAL1 AND C2=LITERAL2) OR (C1=LITERAL3 AND C2=LITERAL4)

这个例子解释说明了列组统计信息对一个合格的 OR 谓词的影响。考虑在 EMPLOYEE 表格上执行如下查询:

清单 27. 在 EMPLOYEE 表上执行的查询
SELECT FIRSTNME, LASTNAME, WORKDEPT, JOB, BONUS, SALARY
  FROM EMPLOYEE
  WHERE ( WORKDEPT='E21' AND JOB='FIELDREP' ) OR 
      ( WORKDEPT='D21' AND JOB='MANAGER' )
  ORDER BY WORKDEPT, SALARY

该查询从 EMPLOYEE 表返回 6 条记录:

清单 28. EMPLOYEE 表产生的查询结果
FIRSTNME     LASTNAME        WORKDEPT JOB      BONUS       SALARY
------------ --------------- -------- -------- ----------- -----------
EVA          PULASKI         D21      MANAGER       700.00    96170.00
ROY          ALONZO          E21      FIELDREP      500.00    31840.00
HELENA       WONG            E21      FIELDREP      500.00    35370.00
RAMLAL       MEHTA           E21      FIELDREP      400.00    39950.00
JASON        GOUNOT          E21      FIELDREP      500.00    43840.00
WING         LEE             E21      FIELDREP      500.00    45370.00

 6 record(s) selected.

如果你使用如下的代码:

RUNSTATS ON TABLE SKAPOOR.EMPLOYEE 
   WITH DISTRIBUTION AND DETAILED INDEXES ALL

在没有列组统计信息的情况下再次收集统计信息的话,优化器会选择一个类似如下所示的查询访问计划,它的基数估计值小于 2:

清单 29. 优化器所选择的查询访问计划
           Rows
          RETURN
          (   1)
           Cost
            I/O
            |
          1.88095
          TBSCAN
          (   2)
          16.1786
             1
            |
          1.88095
          SORT
          (   3)
          16.1272
             1
            |
          1.88095
          TBSCAN
          (   4)
          16.0113
             1
            |
            42
      TABLE: SKAPOOR
         EMPLOYEE

在列 (JOB,WORKDEPT) 上收集列组统计信息能够让优化器更好地估计 OR 谓词的过滤效果,因为 OR 谓词的每一个子项都在 JOB 和 WORKDEPT 列上应用了一组本地等式谓词。至于确定合适的 RUNSTATS 语句来收集列组统计信息,就留与您做练习。收集了列组统计信息之后,优化器会选择一个类似如下所示的查询访问计划,它的基数估计值提高了,与实际的结果 6 行很接近:

清单 30. 得到了更精确的基数估计值的查询访问计划
           Rows
          RETURN
          (   1)
           Cost
            I/O
            |
            5.6
          TBSCAN
          (   2)
          16.2651
             1
            |
            5.6
          SORT
          (   3)
          16.2136
             1
            |
            5.6
          TBSCAN
          (   4)
          16.0113
             1
            |
            42
      TABLE: SKAPOOR
         EMPLOYEE

结束语

优化器凭借精确的基数估计值来准确计算出每一个待定查询访问计划的成本。您可以利用 DB2 9.5 中的多列统计信息的扩展用途来为优化器提供更多的信息,从而使优化器更好地估计基数,选择最佳的查询访问计划。

参考资料

学习

获得产品和技术

  • 下载 DB2 Enterprise 9 的免费试用版。
  • 您现在可以免费使用 DB2。下载 DB2 Express-C,这是为社区提供的 DB2 Express Edition 的免费版本,它提供了和 DB2 Express Edition 相同的核心数据特性并为构建和部署应用程序奠定了坚实的基础。
  • 下载 IBM 产品评估版,并开始使用来自 DB2®、Lotus®、Rational®、Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。

讨论

条评论

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=357455
ArticleTitle=进一步理解 DB2 中的列组统计信息
publish-date=12082008