理解 DB2 中的列组统计信息-技术开发专区

理解 DB2 中的列组统计信息

作者：Samir Kapoor；Vincent Corvinelli 编辑：李博（泡泡） 2007-07-19 10:59

确定何时收集列组统计信息以及要收集哪些列组统计信息是比较困难的。这一节将为您介绍一些方法，帮助您确定何时需要列组统计信息。

本节的示例使用了 SAMPLE 数据库，可以通过执行 “db2sampl” 创建，此数据库使用 db2level：

Informational tokens are "DB2 v8.1.1.120", "s060801", "U808888", and FixPak "13".

示例 3：本地等式谓词

创建了 SAMPLE 数据库后，并没有在表上收集统计信息。首先，需要在 EMPLOYEE 表中收集统计信息：

RUNSTATS ON TABLE SCHEMA_NAME.EMPLOYEE WITH DISTRIBUTION;

考虑 SAMPLE 数据库中 EMPLOYEE 表上的如下查询：

SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY
            FROM EMPLOYEE
            WHERE JOB='ANALYST';

该查询从 EMPLOYEE 表返回两条记录：

EMPNO  FIRSTNME     LASTNAME        WORKDEPT SEX JOB      SALARY
            ------ ------------ --------------- -------- --- -------- -----------
            000130 DOLORES      QUINTANA        C01      F   ANALYST     23800.00
            000140 HEATHER      NICHOLLS        C01      F   ANALYST     28420.00
            2 record(s) selected.

看一下为此查询选择的查询执行计划。

为此，使用 EXPLAIN 工具，它要求 EXPLAIN 表存在。

为了创建 EXPLAIN 表，执行以下代码：

db2 -tvf $DB2PATH/misc/EXPLAIN.DDL

创建了 EXPLAIN 表之后，像下面这样对查询进行 EXPLAIN 处理：

SET CURRENT EXPLAIN MODE EXPLAIN;
                SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY
                FROM EMPLOYEE
                WHERE JOB='ANALYST';
                SET CURRENT EXPLAIN MODE NO;

使用 db2exfmt 工具查看查询执行计划：

db2exfmt -d <DBNAME> -1 -g -o <FILENAME>

使用您喜爱的文本编辑器，您应看到像下面这样的查询执行计划：

        2
                TBSCAN
                (   2)
                30.8464
                2
                |
                32
                TABLE: SKAPOOR
                EMPLOYEE

基数估计值 2 符合实际结果。

为这个查询添加几个冗余的等式谓词：

SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY
                FROM EMPLOYEE
                WHERE JOB='ANALYST' AND SEX='F' AND WORKDEPT='C01';

此查询返回和上面相同的结果集。但是看一下 EXPLAIN 工具生成的查询执行计划，基数估计值并不符合实际结果：

    0.0761719
                TBSCAN
                (   2)
                31.4115
                2
                |
                32
                TABLE: SKAPOOR
                EMPLOYEE

DB2 优化器假设这三个谓词是彼此独立的，因为不存在相关的索引或列组统计信息。

在 JOB、WORKDEPT 和 SEX 列中收集列组统计信息。

RUNSTATS ON TABLE <SCHEMA_NAME>.EMPLOYEE ON ALL COLUMNS AND COLUMNS
                ((JOB, WORKDEPT, SEX)) WITH DISTRIBUTION;

使用这三个列的列组统计信息，DB2 优化器计算出一个更准确的基数估计值：

     1.77778
                TBSCAN
                (   2)
                31.4214
                2
                |
                32
                TABLE: SKAPOOR
                EMPLOYEE

与单一谓词查询计算出的结果不同，所计算出的基数估计值并不是 2，这是因为列组统计信息是一个一致分布统计信息。

示例 4：等式连接谓词

这个示例集中关注表 ORG 和 STAFF 的连接。首先，需要在这两个表上收集统计信息。现在，已经收集好了基本的统计信息。

RUNSTATS ON TABLE <SCHEMA_NAME>.ORG;
            RUNSTATS ON TABLE <SCHEMA_NAME>.STAFF;

下面的查询连接 ORG 和 STAFF 表：

SELECT STAFF.NAME, ORG.DEPTNUMB, ORG.DEPTNAME, STAFF.SALARY
            FROM ORG, STAFF
            WHERE ORG.MANAGER = STAFF.ID AND ORG.DEPTNUMB = STAFF.DEPT
            ORDER BY ORG.DEPTNUMB;

这个查询返回 8 个记录：

NAME      DEPTNUMB DEPTNAME       SALARY
            --------- -------- -------------- ---------
            Molinare        10 Head Office     22959.20
            Hanes           15 New England     20659.80
            Sanders         20 Mid Atlantic    18357.50
            Marenghi        38 South Atlantic  17506.75
            Plotz           42 Great Lakes     18352.80
            Fraye           51 Plains          21150.00
            Lea             66 Pacific         18555.50
            Quill           84 Mountain        19818.00
            8 record(s) selected.

使用 EXPLAIN 工具查看查询执行计划：

                1
                TBSCAN
                (   2)
                33.2225
                2
                |
                1
                SORT
                (   3)
                33.151
                2
                |
                1
                HSJOIN
                (   4)
                33.0248
                2
                /-----+-----\
                35                8
                TBSCAN           TBSCAN
                (   5)           (   6)
                17.2334          15.3736
                1                1
                |                |
                35                8
                TABLE: SKAPOOR  TABLE: SKAPOOR
                STAFF             ORG

这个示例使用了收集列组统计信息的简单方法。附录 B 包含一些示例，对判定连接中的父表做了进一步的分析。

对 STAFF 表的 ID、DEPT 列和 ORG 表的 MANAGER、DEPTNUMB 列做了连接，所以要在这两组列中收集列组统计信息：

RUNSTATS ON TABLE <SCHEMA_NAME>.STAFF ON ALL COLUMNS AND COLUMNS ((ID, DEPT));
                RUNSTATS ON TABLE <SCHEMA_NAME>.ORG ON ALL COLUMNS AND COLUMNS ((MANAGER, DEPTNUMB));

DB2 优化器使用收集到的列组统计信息正确地估计了基数：

               8
            TBSCAN
            (   2)
            33.5658
            2
            |
            8
            SORT
            (   3)
            33.4243
            2
            |
            8
            HSJOIN
            (   4)
            33.0363
            2
            /-----+-----\
            35                8
            TBSCAN           TBSCAN
            (   5)           (   6)
            17.2334          15.3736
            1                1
            |                |
            35                8
            TABLE: SKAPOOR  TABLE: SKAPOOR
            STAFF             ORG

示例 5：查看多列统计信息

优化器利用两种类型的多列统计信息：索引 keycard 统计信息和列组统计信息。这个示例提供了查看表中可用多列统计信息的步骤。

选项 1. 使用 db2look 工具

db2look 工具用来生成 DDL 语句，从而重新创建数据库中定义的对象。可以使用 -m 选项来显示为这些对象收集的统计信息。

在 ORG 表中收集列组统计信息和索引统计信息：

CREATE INDEX IX1 ON ORG (DEPTNUMB, DEPTNAME, MANAGER);
                RUNSTATS ON TABLE <SCHEMA_NAME>.ORG ON ALL COLUMNS
                AND COLUMNS ((DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION))
                WITH DISTRIBUTION AND INDEXES ALL;

使用 db2look 工具查看 ORG 表的统计信息：
db2look -d sample -e -a -m -t ORG -o org.ddl
注意：使用 -h 选项查看关于 DB2look 工具的信息。

在 org.ddl 文件中查看输出。它应该包含如下用于列组统计信息的 UPDATE 语句：

UPDATE SYSSTAT.COLGROUPS SET colgroupcard = 8
                WHERE colgroupid IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR ' AND tabname = 'ORG'
                AND colname = 'DEPTNUMB' AND oridnal = 1)
                AND colgroupid IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR ' AND tabname = 'ORG'
                AND colname = 'DEPTNAME' AND oridnal = 2)
                AND colgroupid IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR ' AND tabname = 'ORG'
                AND colname = 'MANAGER' AND oridnal = 3)
                AND colgroupid IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR ' AND tabname = 'ORG'
                AND colname = 'DIVISION' AND oridnal = 4)
                AND colgroupid IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR ' AND tabname = 'ORG'
                AND colname = 'LOCATION' AND oridnal = 5)
                AND colgroupid NOT IN (SELECT colgroupid
                FROM SYSCAT.COLGROUPCOLS
                WHERE tabschema = 'SKAPOOR '
                AND tabname = 'ORG' AND oridnal = 6) ;

注意：在 V8 FixPak 13 中，列组统计信息添加到了 db2look 工具中。

上面的 update 语句列出了 SYSCAT.COLGROUPCOLS 视图的所有列，以及来自 SYSSTAT.COLGROUPS 的相关多列统计信息，它表示列集中不同组的数量。在这个示例中，以上的语句描述了列集（DEPTNUMB、DEPTNAME、MANAGER、 DIVISION 和 LOCATION）具有八个不同的组。

org.ddl 文件也包含如下用于索引统计信息的语句：

UPDATE SYSSTAT.INDEXES
                SET NLEAF=1,
                NLEVELS=1,
                FIRSTKEYCARD=8,
                FIRST2KEYCARD=8,
                FIRST3KEYCARD=8,
                FIRST4KEYCARD=-1,
                FULLKEYCARD=8,
                CLUSTERFACTOR=-1.000000,
                CLUSTERRATIO=100,
                SEQUENTIAL_PAGES=0,
                DENSITY=0,
                AVERAGE_SEQUENCE_GAP=0.000000,
                AVERAGE_SEQUENCE_FETCH_GAP=0.000000,
                AVERAGE_SEQUENCE_PAGES=0.000000,
                AVERAGE_SEQUENCE_FETCH_PAGES=0.000000,
                AVERAGE_RANDOM_PAGES=1.000000,
                AVERAGE_RANDOM_FETCH_PAGES=0.000000,
                NUMRIDS=8,
                NUMRIDS_DELETED=0,
                NUM_EMPTY_LEAFS=0
                WHERE INDNAME = 'IX1' AND INDSCHEMA = 'SKAPOOR'
                AND TABNAME = 'ORG' AND TABSCHEMA = 'SKAPOOR';

以上的 update 语句描述了下列多列统计信息。FIRST2KEYCARD 统计信息描述了列（DEPTNUMB，DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME，MANAGER）中不同组的数量。FIRST4KEYCARD 的值为 -1，这是因为索引在该键中只有 3 列。

选项 2. 查询目录表

可以从 DB2 目录表中查询与 DB2look 工具输出中所描述的相同的信息Y

如果尚未创建索引，请按选项 1 中的步骤 1 创建索引，在多个表中收集多个列组统计信息：

RUNSTATS ON TABLE <SCHEMA_NAME>.ORG ON ALL COLUMNS
                AND COLUMNS ((DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION),
                (DEPTNUMB, DEPTNAME), (MANAGER, DIVISION)) WITH DISTRIBUTION AND INDEXES ALL;
                RUNSTATS ON TABLE <SCHEMA_NAME>.EMPLOYEE ON ALL COLUMNS
                AND COLUMNS ((EMPNO, WORKDEPT), (EMPNO, WORKDEPT, JOB));

查询目录表来检索列组统计信息。注意下面的查询是一个递归 SQL 语句，它会导致一个可以忽略的 SQL0347W 警告。可以使用 “UPDATE COMMAND OPTIONS USING W OFF” 选项阻止该警告出现。

WITH TMP(ID, NUM) AS
                (
                SELECT COLGROUPID, MAX(ORIDNAL)
                FROM SYSCAT.COLGROUPCOLS
                GROUP BY COLGROUPID
                ),
                TMP2 (ID, NAME, NUM, TNAME, TSCHEMA) AS
                (
                SELECT ID, CAST(RTRIM(COLNAME) AS CHAR(128)), NUM, TABNAME, TABSCHEMA
                FROM TMP Y, SYSCAT.COLGROUPCOLS X
                WHERE X.COLGROUPID = Y.ID
                AND Y.NUM = X.ORIDNAL
                UNION ALL
                SELECT Y.ID, CAST(RTRIM(X.COLNAME) || ',' || Y.NAME AS CHAR(128)), Y.NUM-1,
                TNAME, TSCHEMA
                FROM TMP2 Y, SYSCAT.COLGROUPCOLS X
                WHERE Y.ID=X.COLGROUPID
                AND X.ORIDNAL=Y.NUM-1
                AND Y.NUM > 1
                AND TNAME = TABNAME
                AND TSCHEMA = TABSCHEMA
                )
                SELECT SUBSTR(TSCHEMA,1,10) TABSCHEMA, SUBSTR(TNAME,1,10) TABNAME,
                NAME COLS, COLGROUPCARD
                FROM TMP2, SYSSTAT.COLGROUPS
                WHERE ID = COLGROUPID
                AND NUM = 1
                ORDER BY TABSCHEMA, TABNAME
                ;

上面的查询返回如下记录：

注意：TABSCHEMA 列中的值将是不同的。同样，COLS 结果列并强制转换为 CHAR(128)，如果结果超过 128 个字符，它会将结果截断。在这个例子中，可能需要将 CAST 修改为一个更大的字符串。

TABSCHEMA  TABNAME    COLS                                   COLGROUPCARD
                ---------- ---------- ------------------------------//------ -----------------
                SKAPOOR    EMPLOYEE   EMPNO,WORKDEPT                                        32
                SKAPOOR    EMPLOYEE   EMPNO,WORKDEPT,JOB                                    32
                SKAPOOR    ORG        DEPTNUMB,DEPTNAME                                      8
                SKAPOOR    ORG        MANAGER,DIVISION                                       8
                SKAPOOR    ORG        DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION            8
                5 record(s) selected with 1 warning messages suppressed.

这些记录描述了 EMPLOYEE 表的两列组统计信息和 ORG 表的三列组统计信息。

注意：在上面的查询中，注意 SYSCAT.SYSCOLGROUPCOLS 视图中的名为 “ORIDNAL” 的列。在 DB2 9 中，其拼写改为了 “ORDINAL”，所以，这个查询需要按照在 DB2 9 中使用的方法更新，如下所示：

WITH TMP(ID, NUM) AS
                (
                SELECT COLGROUPID, MAX(ORDINAL)
                FROM SYSCAT.COLGROUPCOLS
                GROUP BY COLGROUPID
                ),
                TMP2 (ID, NAME, NUM, TNAME, TSCHEMA) AS
                (
                SELECT ID, CAST(RTRIM(COLNAME) AS CHAR(128)), NUM, TABNAME, TABSCHEMA
                FROM TMP Y, SYSCAT.COLGROUPCOLS X
                WHERE X.COLGROUPID = Y.ID
                AND Y.NUM = X.ORDINAL
                UNION ALL
                SELECT Y.ID, CAST(RTRIM(X.COLNAME) || ',' || Y.NAME AS CHAR(128)), Y.NUM-1,
                TNAME, TSCHEMA
                FROM TMP2 Y, SYSCAT.COLGROUPCOLS X
                WHERE Y.ID=X.COLGROUPID
                AND X.ORDINAL=Y.NUM-1
                AND Y.NUM > 1
                AND TNAME = TABNAME
                AND TSCHEMA = TABSCHEMA
                )
                SELECT SUBSTR(TSCHEMA,1,10) TABSCHEMA, SUBSTR(TNAME,1,10) TABNAME, NAME COLS,
                COLGROUPCARD
                FROM TMP2, SYSSTAT.COLGROUPS
                WHERE ID = COLGROUPID
                AND NUM = 1
                ORDER BY TABSCHEMA, TABNAME
                ;

查询目录表以检索索引统计信息。

注意：在 TABSCHEMA='SKAPOOR' 谓词中使用合适的模式名替换所提供的值。

SELECT SUBSTR(COLNAMES, 1, 30) AS COLS, FIRST2KEYCARD, FIRST3KEYCARD,
                FIRST4KEYCARD, FULLKEYCARD
                FROM SYSSTAT.INDEXES
                WHERE TABSCHEMA='SKAPOOR' AND TABNAME='ORG';

上述查询返回如下记录：

COLS                       FIRST2KEYCARD FIRST3KEYCARD FIRST4KEYCARD FULLKEYCARD
                -------------------------- ------------- ------------- ------------- -----------
                +DEPTNUMB+DEPTNAME+MANAGER             8             8            -1           8
                1 record(s) selected.

FIRST2KEYCARD 统计信息描述了在列（DEPTNUMB 和 DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME 和 MANAGER）中不同组的数量。FIRST4KEYCARD 值为 -1，这是因为索引在键中只包含三列。

第1页：简介第2页：多个本地等式谓词的统计相关性第3页：多个等式连接谓词的统计相关性第4页：收集多列统计信息第5页：确定何时收集列组统计信息第6页：练习第7页：附录 A. 自动统计信息分析第8页：附录 B. 确定两个连接表中的父表第9页：作者简介

关注我们