神马文学网理解DB2中的列组统计信息
来源：百度文库 编辑：神马文学网 时间：2024/04/27 18:04:43
使用 IBM DB2® for Linux®，UNIX®，and Windows® （DB2）中的列组统计信息，当多个等式本地谓词或等式连接谓词互相关联时，优化器可以确定一个更好的查询访问计划，并且改善查询性能。本文将介绍如何使用列组统计信息。
DB2 SQL 优化器（后文简称为优化器）可以估计每个备选访问计划的执行成本，并根据其估计结果选择一个最佳访问计划。一个访问计划可以指定用来解析一条 SQL 语句的操作次序。
为正确地确定每种访问计划的成本，DB2 优化器需要准确的基数估计值。基数估计是这样一种过程：在应用了谓词或执行了聚集之后，优化器使用统计信息确定部分查询结果的大小。对于访问计划的每个操作符，优化器将估计该操作符的基数输出。一个或更多谓词的应用可以减少输出流基数。
在计算谓词对于基数估计值的组合过滤效果时，通常会假设这些谓词彼此之间是独立的。然而，这些谓词可以在统计方面彼此关联。单独地处理它们通常会导致优化器低估基数值。而基数值的低估又会导致优化器选择一个次优的访问计划。
对于至少应用了至少两个本地等式谓词的 SQL 语句，优化器将考虑使用多列统计信息来检测统计关联，并更加准确地估计多个谓词组合的过滤效果。同样对于连接两个或更多表的 SQL 语句，以及在一对表间至少使用了两个等式连接谓词的连接，优化器也会使用多列统计信息。
一个本地等式谓词是一个应用于单个表的等式谓词，其描述如下所示：
COLUMN = literal
其中 literal 可以是以下任一内容：
一个常量值； 一个参数标记或一个主变量； 一个专用寄存器（例如，CURRENT DATE）
一个等式连接谓词的描述如下所示，它用于表 1 和表 2 间的连接：
T1.COLUMN = T2.COLUMN
DB2 V8.2 使用下面的多列统计信息：
索引 keycard 统计信息：FIRST2KEYCARD、FIRST3KEYCARD、FIRST4KEYCARD 和 FULLKEYCARD 列组统计信息：列组基数值
这些统计信息描述了包含两个或更多列的列集中不同分组的数量。
在 DB2 V8.2 出现之前，只能使用索引 keycard 统计信息，并且要受下列条件约束：
索引必须是完全限定的。如果键中的所有列都可以被等式谓词 引用（连接谓词或本地谓词，但不是两者的混合），那么这个索引就是完全限定的。 对于连接谓词，索引也必须是惟一的。
在 DB2 V8.2 中，通过考虑到所有索引 keycard 统计信息而不要求索引完全限定，DB2 SQL 优化器进一步扩展了多列统计信息的使用。它还考虑到了用户收集的任何列组统计信息。本文讨论了优化器如何利用这些统计信息，以及用户如何识别要收集的列组统计信息。
9W9itug
9W9itug
DB2 SQL 优化器试图检测多个本地等式谓词间的统计相关性。
示例 1：假设有一个表 SHOW_LISTINGS，它包含如下列：
9W9itug
9W9itug
列名 描述
SHOW_ID 表外键，包含关于各演出清单的信息
CHANNEL_ID 表外键，包含关于播放演出的每个频道的信息
STATION_ID 表外键，包含和频道相关的每个电视台的信息
CITY_ID 表外键，包含关于上演该演出的每个城市的信息
DAY 演出播放的日期
TIME 演出播放的当天的时间
描述了演出清单的其他属性
由于演出只在某个电视台的某个频道播出，在一天的特定时间内，这些列（SHOW_ID，CHANNEL_ID，STATION_ID 和 TIME）彼此之间不是互相独立的。DAY 列独立于 TIME 列，但是它不独立于所有演出清单的 SHOW_ID。
设想一条应用了以下谓词的 SQL 语句：
P1: SHOW_ID = ? P2: CHANNEL_ID = ? P3: STATION_ID = ? P4: TIME = ?
9W9itug
如果存在这样一个索引，其中的键包含谓词 P1-P4 引用的所有列，或者键中的前四列包含所引用的列，优化器将使用 FIRST4KEYCARD 索引统计信息（如果收集了索引统计信息的话），来检测谓词 P1-P4 之间的统计相关性。在应用了这四个谓词之后，优化器将计算一个更准确的基数估计值。例如，下面的任何一个索引可以用来检测这四个谓词间的统计相关性：
IX1 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME) IX2 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME, CITY_ID) IX3 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME, DAY, CITY_ID)
9W9itug
优化器使用 IX1 的 FULLKEYCARD 和 FIRST4KEYCARD 统计信息来检测所有四个谓词的相关性。类似地，它也可以使用 IX2 和 IX3 的 FIRST4KEYCARD。
不能使用如下所示的索引：
IX4 ON SHOW_LISTINGS(CITY_ID, SHOW_ID, CHANNEL_ID, STATION_ID, TIME)
9W9itug
由于每个 keycard 统计信息都将包含 CITY_ID 列，因此该列上未定义本地等式谓词。
如果不存在具有所需键的索引，那么可以在 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 上收集列组统计信息。优化器使用这些列组统计信息，运用与处理索引的 FIRST4KEYCARD 统计信息相同的方式检测这四个谓词间的统计相关性。
DB2 优化器同样也会考虑具有谓词子集的索引或者列组统计信息。例如，看下面所示的索引：
IX5 ON SHOW_LISTINGS(SHOW_ID, STATION_ID, CITY_ID, CHANNEL_ID)
9W9itug
该索引不能使用 FIRST2KEYCARD 来检测谓词 P1 和 P3 的统计相关性。尽管只是部分纠正了谓词 P1-P4 间的统计相关性，但已足以允许优化器选择一个优秀的查询执行计划。此外，尽管索引 IX5 中的完整键包含 CHANNEL_ID 列，仍然不能使用 FULLKEYCARD 统计信息来检测与 P2 的相关性，因为它包含的 CITY_ID 列没有在谓词中引用。
9W9itug
9W9itug
DB2 SQL 优化器同样尝试检测两个表中的多个等式连接谓词间的统计相关性。
示例 2：考虑示例 1 中描述的SHOW_LISTINGS 表，另外，RATINGS 表包含如下列：
9W9itug
9W9itug
列名 描述
SHOW_ID 表外键，包含关于各演出清单的信息
CHANNEL_ID 表外键，包含关于播放演出的每个频道的信息
STATION_ID 表外键，包含和频道相关的每个电视台的信息
CITY_ID 表外键，包含关于上演该演出的每个城市的信息
DAY 播放演出的日期
TIME 播放演出的时间
RATING 在一天的某个时间，在特定城市中，某个电视台频道的演出清单的收视率
RATINGS 表包含描述不同城市演出清单的收视率信息，用户可能需要查询 RATINGS 表和 SHOW_LISTINGS 表的连接以检索这两个表的属性。一个查询在该连接上应用的谓词集包含以下内容：
P1: SHOW_LISTINGS.SHOW_ID = RATINGS.SHOW_ID P2: SHOW_LISTINGS.CHANNEL_ID = RATINGS.CHANNEL_ID P3: SHOW_LISTINGS.STATION_ID = RATINGS.STATION_ID
9W9itug
P1-P3 这三个谓词可能不是互相独立的；优化器试图使用任何可用的多列统计信息来检测谓词之间的相关性。
优化器只能检测和说明每一对连接表上的多个连接谓词间的统计相关性。例如，如果一条 SQL 语句包含如下谓词：
P1: T1.A = T2.A P2: T1.B = T2.B P3: T1.C = T3.C
9W9itug
优化器只尝试检测 P1 和 P2 间的统计相关性，因为它们只应用于相同的两个表的连接，T1 和 T2。P3 应用于 T1 和 T2 的连接；那是一个不同的连接。如果添加了第四个谓词，例如：
P4: T1.D = T2.D
9W9itug
优化器将进一步尝试检测 P3 和 P4 间的统计相关性。
为了说明等式连接谓词间的统计相关性，优化器使用连接所涉及的两表之一的多列统计信息；这个表被视为连接的父表。因此只需要在连接的父表中收集列组统计信息。连接的另一个表被指定为子表。如果父表不能确定，那么优化器就不能使用多列统计信息来检测连接谓词间的统计相关性。
附录 B 提供了优化器如何确定两个连接表中的父表和子表的具体示例和描述。对连接中的父表进行标识，或者判断连接中是否有父表，这对于避免收集无用的列组统计信息是非常有用的。如果尚无合适的索引可用，那么在连接中的每个表上收集列组统计信息即可。
9W9itug
9W9itug
优化器考虑使用所有可用的索引 keycard 统计信息。如果具有统计相关性的谓词所引用的列集中，索引 keycard 统计信息不可用，可以使用 RUNSTATS 命令收集列组统计信息。下面是从 DB2 V82 提取的语法。
注意：对于 DB2 9，请在Info Center 的 DB2 9 文档中查看语法。.
>>-RUNSTATS--ON TABLE--table name--+-USE PROFILE------------+---> '-| Statistics Options |-' Statistics Options: .-ALLOW WRITE ACCESS-. |--+--------------------------+--+--------------------+---------> '-| Table Object Options |-' '-ALLOW READ ACCESS--' Table Object Options: |--+-FOR--| Index Clause |----------------------------------+---| '-+-------------------------+--+-----------------------+-' '-| Column Stats Clause |-' '-AND--| Index Clause |-' Column Stats Clause: |--+-ON--| Cols Clause |------------------------------+---------| '-+---------------------+--| Distribution Clause |-' '-ON--| Cols Clause |-' On Cols Clause: .-ON ALL COLUMNS-------------------------------------------------. | .-,-------------. | | V | | |--+-ON--+----------------------+--COLUMNS--(----Column Option-+--)-+--| | '-+-ALL-+--COLUMNS AND-' | | '-KEY-' | '-ON KEY COLUMNS-------------------------------------------------'
9W9itug
“ON COLUMNS” 子句允许您指定一个列的列表，为它们收集统计信息。如果您指定了一组列，则会收集该组中惟一值的数量。未列出的列的统计信息非常明确。您可以在 “on-cols-clause” 和 “on-dist-cols-clause” 中使用这个子句。
注意：目前还不支持为一组列收集分布统计信息。
注意：如果启用了 automatic runstats 并使用 RUNSTATS 命令收集了列组统计信息，automatic runstats 将覆盖这些统计信息，并丢弃列组统计信息。
AUTO_RUNSTATS 数据库配置设置表明是否启用了 automatic runstats：
Automatic runstats (AUTO_RUNSTATS)=ON
9W9itug
如果设置为 “ON”，则启用 automatic runstats。
AUTO_RUNSTATS 默认情况下使用 “具有所有分布和示例的详细索引” 的 RUNSTATS 选项。
您可以使用统计信息配置文件来覆盖默认的 RUNSTATS 选项。您可以将希望收集的任意列组统计信息添加到此配置文件中，以避免 automatic runstats 覆盖它们。
“统计信息配置文件” 提供了关于统计信息配置文件的详细资料。
对于数据库分区特性（database partitioning feature，DPF）环境，automatic runstats 总是在目录分区上收集统计信息。如果表不存在于这个目录节点上，将使用表驻留的节点组的第一个分区。
将使用 RUNSTATS 命令的 “ON COLUMNS” 选项收集列组统计信息。例如，使用SHOW_LISTINGS 表收集 SHOW_LISTINGS.SHOW_ID、SHOW_LISTINGS.CHANNEL_ID 和 SHOW_LISTINGS.STATION_ID 中的列组统计信息，执行如下 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID))
9W9itug
如果只在 DAY 列收集列统计信息以及收集 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 列的列组统计信息，则执行以下的 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID, TIME), DAY)
9W9itug
注意：列统计信息是在列组中列出的所有列上收集的。在上面的命令中，列统计信息同样是在每一个列中收集的（SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME）。
为了收集所有列的统计信息以及在 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 中收集列组统计信息，执行下面的 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON ALL COLUMNS AND ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID, TIME))
9W9itug
要收集多个多列统计信息，可以提供一个组集。下面的 RUNSTATS 命令在组（SHOW_ID、CHANNEL_ID 和 STATION_ID）和组（SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME）中收集多列统计信息，同样也在 DAY 列上收集了列统计信息：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID), (SHOW_ID, CHANNEL_ID, STATION_ID, TIME), DAY)
9W9itug
注意：对于索引统计信息来说，将为索引键的前两个列、前三列和前四列收集多个多列统计信息，而一个列组基数统计信息是为指定的各列组收集的。
9W9itug
9W9itug
确定何时收集列组统计信息以及要收集哪些列组统计信息是比较困难的。这一节将为您介绍一些方法，帮助您确定何时需要列组统计信息。
本节的示例使用了 SAMPLE 数据库，可以通过执行 “db2sampl” 创建，此数据库使用 db2level：
Informational tokens are "DB2 v8.1.1.120", "s060801", "U808888", and FixPak "13".
9W9itug
示例 3：本地等式谓词
创建了 SAMPLE 数据库后，并没有在表上收集统计信息。首先，需要在 EMPLOYEE 表中收集统计信息：
RUNSTATS ON TABLE SCHEMA_NAME.EMPLOYEE WITH DISTRIBUTION;
9W9itug
考虑 SAMPLE 数据库中 EMPLOYEE 表上的如下查询：
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY FROM EMPLOYEE WHERE JOB='ANALYST';
9W9itug
该查询从 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.
9W9itug
看一下为此查询选择的查询执行计划。
为此，使用 EXPLAIN 工具，它要求 EXPLAIN 表存在。
为了创建 EXPLAIN 表，执行以下代码： db2 -tvf $DB2PATH/misc/EXPLAIN.DDL
9W9itug
创建了 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;
9W9itug
使用 db2exfmt 工具查看查询执行计划： db2exfmt -d -1 -g -o
9W9itug
使用您喜爱的文本编辑器，您应看到像下面这样的查询执行计划： 2 TBSCAN ( 2) 30.8464 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
基数估计值 2 符合实际结果。
为这个查询添加几个冗余的等式谓词 ： SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY FROM EMPLOYEE WHERE JOB='ANALYST' AND SEX='F' AND WORKDEPT='C01';
9W9itug
此查询返回和上面相同的结果集。但是看一下 EXPLAIN 工具生成的查询执行计划，基数估计值并不符合实际结果：
0.0761719 TBSCAN ( 2) 31.4115 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
DB2 优化器假设这三个谓词是彼此独立的，因为不存在相关的索引或列组统计信息。
在 JOB、WORKDEPT 和 SEX 列中收集列组统计信息。 RUNSTATS ON TABLE.EMPLOYEE ON ALL COLUMNS AND COLUMNS ((JOB, WORKDEPT, SEX)) WITH DISTRIBUTION;
9W9itug
使用这三个列的列组统计信息，DB2 优化器计算出一个更准确的基数估计值：
1.77778 TBSCAN ( 2) 31.4214 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
与单一谓词查询计算出的结果不同，所计算出的基数估计值并不是 2，这是因为列组统计信息是一个一致分布统计信息。
示例 4：等式连接谓词
这个示例集中关注表 ORG 和 STAFF 的连接。首先，需要在这两个表上收集统计信息。现在，已经收集好了基本的统计信息。
RUNSTATS ON TABLE.ORG; RUNSTATS ON TABLE .STAFF;
9W9itug
下面的查询连接 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;
9W9itug
这个查询返回 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.
9W9itug
使用 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
9W9itug
这个示例使用了收集列组统计信息的简单方法。附录 B 包含一些示例，对判定连接中的父表做了进一步的分析。
对 STAFF 表的 ID、DEPT 列和 ORG 表的 MANAGER、DEPTNUMB 列做了连接，所以要在这两组列中收集列组统计信息： RUNSTATS ON TABLE.STAFF ON ALL COLUMNS AND COLUMNS ((ID, DEPT)); RUNSTATS ON TABLE .ORG ON ALL COLUMNS AND COLUMNS ((MANAGER, DEPTNUMB));
9W9itug
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
9W9itug
示例 5：查看多列统计信息
优化器利用两种类型的多列统计信息：索引 keycard 统计信息和列组统计信息。这个示例提供了查看表中可用多列统计信息的步骤。
选项 1. 使用 db2look 工具
db2look 工具用来生成 DDL 语句，从而重新创建数据库中定义的对象。可以使用 -m 选项来显示为这些对象收集的统计信息。
在 ORG 表中收集列组统计信息和索引统计信息： CREATE INDEX IX1 ON ORG (DEPTNUMB, DEPTNAME, MANAGER); RUNSTATS ON TABLE.ORG ON ALL COLUMNS AND COLUMNS ((DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION)) WITH DISTRIBUTION AND INDEXES ALL;
9W9itug
使用 db2look 工具查看 ORG 表的统计信息： db2look -d sample -e -a -m -t ORG -o org.ddl
9W9itug
注意：使用 -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) ;
9W9itug
注意：在 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';
9W9itug
以上的 update 语句描述了下列多列统计信息。FIRST2KEYCARD 统计信息描述了列（DEPTNUMB，DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME，MANAGER）中不同组的数量。FIRST4KEYCARD 的值为 -1，这是因为索引在该键中只有 3 列。
选项 2. 查询目录表
可以从 DB2 目录表中查询与 DB2look 工具输出中所描述的相同的信息Y
如果尚未创建索引，请按选项 1 中的步骤 1 创建索引，在多个表中收集多个列组统计信息： RUNSTATS ON TABLE.ORG ON ALL COLUMNS AND COLUMNS ((DEPTNUMB, DEPTNAME, MANAGER, DIVISION, LOCATION), (DEPTNUMB, DEPTNAME), (MANAGER, DIVISION)) WITH DISTRIBUTION AND INDEXES ALL; RUNSTATS ON TABLE .EMPLOYEE ON ALL COLUMNS AND COLUMNS ((EMPNO, WORKDEPT), (EMPNO, WORKDEPT, JOB));
9W9itug
查询目录表来检索列组统计信息。注意下面的查询是一个递归 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 ;
9W9itug
上面的查询返回如下记录：
注意：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.
9W9itug
这些记录描述了 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 ;
9W9itug
查询目录表以检索索引统计信息。
注意：在 TABSCHEMA='SKAPOOR' 谓词中使用合适的模式名替换所提供的值。
SELECT SUBSTR(COLNAMES, 1, 30) AS COLS, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD FROM SYSSTAT.INDEXES WHERE TABSCHEMA='SKAPOOR' AND TABNAME='ORG';
9W9itug
上述查询返回如下记录：
COLS FIRST2KEYCARD FIRST3KEYCARD FIRST4KEYCARD FULLKEYCARD -------------------------- ------------- ------------- ------------- ----------- +DEPTNUMB+DEPTNAME+MANAGER 8 8 -1 8 1 record(s) selected.
9W9itug
FIRST2KEYCARD 统计信息描述了在列（DEPTNUMB 和 DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME 和 MANAGER）中不同组的数量。FIRST4KEYCARD 值为 -1，这是因为索引在键中只包含三列。
DB2 SQL 优化器（后文简称为优化器）可以估计每个备选访问计划的执行成本，并根据其估计结果选择一个最佳访问计划。一个访问计划可以指定用来解析一条 SQL 语句的操作次序。
为正确地确定每种访问计划的成本，DB2 优化器需要准确的基数估计值。基数估计是这样一种过程：在应用了谓词或执行了聚集之后，优化器使用统计信息确定部分查询结果的大小。对于访问计划的每个操作符，优化器将估计该操作符的基数输出。一个或更多谓词的应用可以减少输出流基数。
在计算谓词对于基数估计值的组合过滤效果时，通常会假设这些谓词彼此之间是独立的。然而，这些谓词可以在统计方面彼此关联。单独地处理它们通常会导致优化器低估基数值。而基数值的低估又会导致优化器选择一个次优的访问计划。
对于至少应用了至少两个本地等式谓词的 SQL 语句，优化器将考虑使用多列统计信息来检测统计关联，并更加准确地估计多个谓词组合的过滤效果。同样对于连接两个或更多表的 SQL 语句，以及在一对表间至少使用了两个等式连接谓词的连接，优化器也会使用多列统计信息。
一个本地等式谓词是一个应用于单个表的等式谓词，其描述如下所示：
COLUMN = literal其中 literal 可以是以下任一内容：
一个常量值； 一个参数标记或一个主变量； 一个专用寄存器（例如，CURRENT DATE）
一个等式连接谓词的描述如下所示，它用于表 1 和表 2 间的连接：
T1.COLUMN = T2.COLUMNDB2 V8.2 使用下面的多列统计信息：
索引 keycard 统计信息：FIRST2KEYCARD、FIRST3KEYCARD、FIRST4KEYCARD 和 FULLKEYCARD 列组统计信息：列组基数值
这些统计信息描述了包含两个或更多列的列集中不同分组的数量。
在 DB2 V8.2 出现之前，只能使用索引 keycard 统计信息，并且要受下列条件约束：
索引必须是完全限定的。如果键中的所有列都可以被等式谓词 引用（连接谓词或本地谓词，但不是两者的混合），那么这个索引就是完全限定的。 对于连接谓词，索引也必须是惟一的。
在 DB2 V8.2 中，通过考虑到所有索引 keycard 统计信息而不要求索引完全限定，DB2 SQL 优化器进一步扩展了多列统计信息的使用。它还考虑到了用户收集的任何列组统计信息。本文讨论了优化器如何利用这些统计信息，以及用户如何识别要收集的列组统计信息。
9W9itug
9W9itug
DB2 SQL 优化器试图检测多个本地等式谓词间的统计相关性。
示例 1：假设有一个表 SHOW_LISTINGS，它包含如下列：
9W9itug
9W9itug
列名 描述
SHOW_ID 表外键，包含关于各演出清单的信息
CHANNEL_ID 表外键，包含关于播放演出的每个频道的信息
STATION_ID 表外键，包含和频道相关的每个电视台的信息
CITY_ID 表外键，包含关于上演该演出的每个城市的信息
DAY 演出播放的日期
TIME 演出播放的当天的时间
由于演出只在某个电视台的某个频道播出，在一天的特定时间内，这些列（SHOW_ID，CHANNEL_ID，STATION_ID 和 TIME）彼此之间不是互相独立的。DAY 列独立于 TIME 列，但是它不独立于所有演出清单的 SHOW_ID。
设想一条应用了以下谓词的 SQL 语句：
P1: SHOW_ID = ? P2: CHANNEL_ID = ? P3: STATION_ID = ? P4: TIME = ?
9W9itug
如果存在这样一个索引，其中的键包含谓词 P1-P4 引用的所有列，或者键中的前四列包含所引用的列，优化器将使用 FIRST4KEYCARD 索引统计信息（如果收集了索引统计信息的话），来检测谓词 P1-P4 之间的统计相关性。在应用了这四个谓词之后，优化器将计算一个更准确的基数估计值。例如，下面的任何一个索引可以用来检测这四个谓词间的统计相关性：
IX1 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME) IX2 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME, CITY_ID) IX3 ON SHOW_LISTINGS(SHOW_ID, CHANNEL_ID, STATION_ID, TIME, DAY, CITY_ID)
9W9itug
优化器使用 IX1 的 FULLKEYCARD 和 FIRST4KEYCARD 统计信息来检测所有四个谓词的相关性。类似地，它也可以使用 IX2 和 IX3 的 FIRST4KEYCARD。
不能使用如下所示的索引：
IX4 ON SHOW_LISTINGS(CITY_ID, SHOW_ID, CHANNEL_ID, STATION_ID, TIME)
9W9itug
由于每个 keycard 统计信息都将包含 CITY_ID 列，因此该列上未定义本地等式谓词。
如果不存在具有所需键的索引，那么可以在 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 上收集列组统计信息。优化器使用这些列组统计信息，运用与处理索引的 FIRST4KEYCARD 统计信息相同的方式检测这四个谓词间的统计相关性。
DB2 优化器同样也会考虑具有谓词子集的索引或者列组统计信息。例如，看下面所示的索引：
IX5 ON SHOW_LISTINGS(SHOW_ID, STATION_ID, CITY_ID, CHANNEL_ID)
9W9itug
该索引不能使用 FIRST2KEYCARD 来检测谓词 P1 和 P3 的统计相关性。尽管只是部分纠正了谓词 P1-P4 间的统计相关性，但已足以允许优化器选择一个优秀的查询执行计划。此外，尽管索引 IX5 中的完整键包含 CHANNEL_ID 列，仍然不能使用 FULLKEYCARD 统计信息来检测与 P2 的相关性，因为它包含的 CITY_ID 列没有在谓词中引用。
9W9itug
9W9itug
DB2 SQL 优化器同样尝试检测两个表中的多个等式连接谓词间的统计相关性。
示例 2：考虑示例 1 中描述的SHOW_LISTINGS 表，另外，RATINGS 表包含如下列：
9W9itug
9W9itug
列名 描述
SHOW_ID 表外键，包含关于各演出清单的信息
CHANNEL_ID 表外键，包含关于播放演出的每个频道的信息
STATION_ID 表外键，包含和频道相关的每个电视台的信息
CITY_ID 表外键，包含关于上演该演出的每个城市的信息
DAY 播放演出的日期
TIME 播放演出的时间
RATING 在一天的某个时间，在特定城市中，某个电视台频道的演出清单的收视率
RATINGS 表包含描述不同城市演出清单的收视率信息，用户可能需要查询 RATINGS 表和 SHOW_LISTINGS 表的连接以检索这两个表的属性。一个查询在该连接上应用的谓词集包含以下内容：
P1: SHOW_LISTINGS.SHOW_ID = RATINGS.SHOW_ID P2: SHOW_LISTINGS.CHANNEL_ID = RATINGS.CHANNEL_ID P3: SHOW_LISTINGS.STATION_ID = RATINGS.STATION_ID
9W9itug
P1-P3 这三个谓词可能不是互相独立的；优化器试图使用任何可用的多列统计信息来检测谓词之间的相关性。
优化器只能检测和说明每一对连接表上的多个连接谓词间的统计相关性。例如，如果一条 SQL 语句包含如下谓词：
P1: T1.A = T2.A P2: T1.B = T2.B P3: T1.C = T3.C
9W9itug
优化器只尝试检测 P1 和 P2 间的统计相关性，因为它们只应用于相同的两个表的连接，T1 和 T2。P3 应用于 T1 和 T2 的连接；那是一个不同的连接。如果添加了第四个谓词，例如：
P4: T1.D = T2.D
9W9itug
优化器将进一步尝试检测 P3 和 P4 间的统计相关性。
为了说明等式连接谓词间的统计相关性，优化器使用连接所涉及的两表之一的多列统计信息；这个表被视为连接的父表。因此只需要在连接的父表中收集列组统计信息。连接的另一个表被指定为子表。如果父表不能确定，那么优化器就不能使用多列统计信息来检测连接谓词间的统计相关性。
附录 B 提供了优化器如何确定两个连接表中的父表和子表的具体示例和描述。对连接中的父表进行标识，或者判断连接中是否有父表，这对于避免收集无用的列组统计信息是非常有用的。如果尚无合适的索引可用，那么在连接中的每个表上收集列组统计信息即可。
9W9itug
9W9itug
优化器考虑使用所有可用的索引 keycard 统计信息。如果具有统计相关性的谓词所引用的列集中，索引 keycard 统计信息不可用，可以使用 RUNSTATS 命令收集列组统计信息。下面是从 DB2 V82 提取的语法。
注意：对于 DB2 9，请在Info Center 的 DB2 9 文档中查看语法。.
>>-RUNSTATS--ON TABLE--table name--+-USE PROFILE------------+---> '-| Statistics Options |-' Statistics Options: .-ALLOW WRITE ACCESS-. |--+--------------------------+--+--------------------+---------> '-| Table Object Options |-' '-ALLOW READ ACCESS--' Table Object Options: |--+-FOR--| Index Clause |----------------------------------+---| '-+-------------------------+--+-----------------------+-' '-| Column Stats Clause |-' '-AND--| Index Clause |-' Column Stats Clause: |--+-ON--| Cols Clause |------------------------------+---------| '-+---------------------+--| Distribution Clause |-' '-ON--| Cols Clause |-' On Cols Clause: .-ON ALL COLUMNS-------------------------------------------------. | .-,-------------. | | V | | |--+-ON--+----------------------+--COLUMNS--(----Column Option-+--)-+--| | '-+-ALL-+--COLUMNS AND-' | | '-KEY-' | '-ON KEY COLUMNS-------------------------------------------------'
9W9itug
“ON COLUMNS” 子句允许您指定一个列的列表，为它们收集统计信息。如果您指定了一组列，则会收集该组中惟一值的数量。未列出的列的统计信息非常明确。您可以在 “on-cols-clause” 和 “on-dist-cols-clause” 中使用这个子句。
注意：目前还不支持为一组列收集分布统计信息。
注意：如果启用了 automatic runstats 并使用 RUNSTATS 命令收集了列组统计信息，automatic runstats 将覆盖这些统计信息，并丢弃列组统计信息。
AUTO_RUNSTATS 数据库配置设置表明是否启用了 automatic runstats：
Automatic runstats (AUTO_RUNSTATS)=ON
9W9itug
如果设置为 “ON”，则启用 automatic runstats。
AUTO_RUNSTATS 默认情况下使用 “具有所有分布和示例的详细索引” 的 RUNSTATS 选项。
您可以使用统计信息配置文件来覆盖默认的 RUNSTATS 选项。您可以将希望收集的任意列组统计信息添加到此配置文件中，以避免 automatic runstats 覆盖它们。
“统计信息配置文件” 提供了关于统计信息配置文件的详细资料。
对于数据库分区特性（database partitioning feature，DPF）环境，automatic runstats 总是在目录分区上收集统计信息。如果表不存在于这个目录节点上，将使用表驻留的节点组的第一个分区。
将使用 RUNSTATS 命令的 “ON COLUMNS” 选项收集列组统计信息。例如，使用SHOW_LISTINGS 表收集 SHOW_LISTINGS.SHOW_ID、SHOW_LISTINGS.CHANNEL_ID 和 SHOW_LISTINGS.STATION_ID 中的列组统计信息，执行如下 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID))
9W9itug
如果只在 DAY 列收集列统计信息以及收集 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 列的列组统计信息，则执行以下的 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID, TIME), DAY)
9W9itug
注意：列统计信息是在列组中列出的所有列上收集的。在上面的命令中，列统计信息同样是在每一个列中收集的（SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME）。
为了收集所有列的统计信息以及在 SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME 中收集列组统计信息，执行下面的 RUNSTATS 命令：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON ALL COLUMNS AND ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID, TIME))
9W9itug
要收集多个多列统计信息，可以提供一个组集。下面的 RUNSTATS 命令在组（SHOW_ID、CHANNEL_ID 和 STATION_ID）和组（SHOW_ID、CHANNEL_ID、STATION_ID 和 TIME）中收集多列统计信息，同样也在 DAY 列上收集了列统计信息：
RUNSTATS ON TABLE schema_name.SHOW_LISTINGS ON COLUMNS ((SHOW_ID, CHANNEL_ID, STATION_ID), (SHOW_ID, CHANNEL_ID, STATION_ID, TIME), DAY)
9W9itug
注意：对于索引统计信息来说，将为索引键的前两个列、前三列和前四列收集多个多列统计信息，而一个列组基数统计信息是为指定的各列组收集的。
9W9itug
9W9itug
确定何时收集列组统计信息以及要收集哪些列组统计信息是比较困难的。这一节将为您介绍一些方法，帮助您确定何时需要列组统计信息。
本节的示例使用了 SAMPLE 数据库，可以通过执行 “db2sampl” 创建，此数据库使用 db2level：
Informational tokens are "DB2 v8.1.1.120", "s060801", "U808888", and FixPak "13".
9W9itug
示例 3：本地等式谓词
创建了 SAMPLE 数据库后，并没有在表上收集统计信息。首先，需要在 EMPLOYEE 表中收集统计信息：
RUNSTATS ON TABLE SCHEMA_NAME.EMPLOYEE WITH DISTRIBUTION;
9W9itug
考虑 SAMPLE 数据库中 EMPLOYEE 表上的如下查询：
SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY FROM EMPLOYEE WHERE JOB='ANALYST';
9W9itug
该查询从 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.
9W9itug
看一下为此查询选择的查询执行计划。
为此，使用 EXPLAIN 工具，它要求 EXPLAIN 表存在。
为了创建 EXPLAIN 表，执行以下代码： db2 -tvf $DB2PATH/misc/EXPLAIN.DDL
9W9itug
创建了 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;
9W9itug
使用 db2exfmt 工具查看查询执行计划： db2exfmt -d
9W9itug
使用您喜爱的文本编辑器，您应看到像下面这样的查询执行计划： 2 TBSCAN ( 2) 30.8464 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
基数估计值 2 符合实际结果。
为这个查询添加几个冗余的等式谓词 ： SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, SEX, JOB, SALARY FROM EMPLOYEE WHERE JOB='ANALYST' AND SEX='F' AND WORKDEPT='C01';
9W9itug
此查询返回和上面相同的结果集。但是看一下 EXPLAIN 工具生成的查询执行计划，基数估计值并不符合实际结果：
0.0761719 TBSCAN ( 2) 31.4115 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
DB2 优化器假设这三个谓词是彼此独立的，因为不存在相关的索引或列组统计信息。
在 JOB、WORKDEPT 和 SEX 列中收集列组统计信息。 RUNSTATS ON TABLE
9W9itug
使用这三个列的列组统计信息，DB2 优化器计算出一个更准确的基数估计值：
1.77778 TBSCAN ( 2) 31.4214 2 | 32 TABLE: SKAPOOR EMPLOYEE
9W9itug
与单一谓词查询计算出的结果不同，所计算出的基数估计值并不是 2，这是因为列组统计信息是一个一致分布统计信息。
示例 4：等式连接谓词
这个示例集中关注表 ORG 和 STAFF 的连接。首先，需要在这两个表上收集统计信息。现在，已经收集好了基本的统计信息。
RUNSTATS ON TABLE
9W9itug
下面的查询连接 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;
9W9itug
这个查询返回 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.
9W9itug
使用 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
9W9itug
这个示例使用了收集列组统计信息的简单方法。附录 B 包含一些示例，对判定连接中的父表做了进一步的分析。
对 STAFF 表的 ID、DEPT 列和 ORG 表的 MANAGER、DEPTNUMB 列做了连接，所以要在这两组列中收集列组统计信息： RUNSTATS ON TABLE
9W9itug
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
9W9itug
示例 5：查看多列统计信息
优化器利用两种类型的多列统计信息：索引 keycard 统计信息和列组统计信息。这个示例提供了查看表中可用多列统计信息的步骤。
选项 1. 使用 db2look 工具
db2look 工具用来生成 DDL 语句，从而重新创建数据库中定义的对象。可以使用 -m 选项来显示为这些对象收集的统计信息。
在 ORG 表中收集列组统计信息和索引统计信息： CREATE INDEX IX1 ON ORG (DEPTNUMB, DEPTNAME, MANAGER); RUNSTATS ON TABLE
9W9itug
使用 db2look 工具查看 ORG 表的统计信息： db2look -d sample -e -a -m -t ORG -o org.ddl
9W9itug
注意：使用 -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) ;
9W9itug
注意：在 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';
9W9itug
以上的 update 语句描述了下列多列统计信息。FIRST2KEYCARD 统计信息描述了列（DEPTNUMB，DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME，MANAGER）中不同组的数量。FIRST4KEYCARD 的值为 -1，这是因为索引在该键中只有 3 列。
选项 2. 查询目录表
可以从 DB2 目录表中查询与 DB2look 工具输出中所描述的相同的信息Y
如果尚未创建索引，请按选项 1 中的步骤 1 创建索引，在多个表中收集多个列组统计信息： RUNSTATS ON TABLE
9W9itug
查询目录表来检索列组统计信息。注意下面的查询是一个递归 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 ;
9W9itug
上面的查询返回如下记录：
注意：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.
9W9itug
这些记录描述了 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 ;
9W9itug
查询目录表以检索索引统计信息。
注意：在 TABSCHEMA='SKAPOOR' 谓词中使用合适的模式名替换所提供的值。
SELECT SUBSTR(COLNAMES, 1, 30) AS COLS, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD FROM SYSSTAT.INDEXES WHERE TABSCHEMA='SKAPOOR' AND TABNAME='ORG';
9W9itug
上述查询返回如下记录：
COLS FIRST2KEYCARD FIRST3KEYCARD FIRST4KEYCARD FULLKEYCARD -------------------------- ------------- ------------- ------------- ----------- +DEPTNUMB+DEPTNAME+MANAGER 8 8 -1 8 1 record(s) selected.
9W9itug
FIRST2KEYCARD 统计信息描述了在列（DEPTNUMB 和 DEPTNAME）中不同组的数量，FIRST3KEYCARD 和 FULLKEYCARD 描述了完整列集（DEPTNUMB，DEPTNAME 和 MANAGER）中不同组的数量。FIRST4KEYCARD 值为 -1，这是因为索引在键中只包含三列。
理解DB2中的列组统计信息
理解 DB2 Universal Database 的字符转换
理解命令行出错信息
中国的一些统计信息
一张表的统计信息
重组与统计信息 语句
湖南统计信息网 - 信息浏览
DB2
DB2 UDB Version 8.2 中的 RUNSTATS
DB2中的存储（空间）计算
DB2 命令行处理器（CLP）中的常用命令
DB2 基础: 如何从 DB2 UDB 系统编目中获得有用的信息
Wintel联盟挺进信息服务IBM预发布DB2新版本
西游记中的妖怪伤亡统计
DB2 最佳实践: 在 DB2 for Linux, Unix, and Windows 中的行压缩的最佳实践
美国农业统计和信息服务
Oracle统计信息与子分区
深度解析DB2数据统计与分析系统优化-IT168 技术开发专区
统计局长回复市民来信：人口普查不易 望理解配合
名言中的交错配列艺术
八字中的婚姻信息
八字中的财富信息
学习中的“理解”和“悟”
理解网页中的视觉层次