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

理解 DB2 中的列组统计信息

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

多个等式谓词的统计相关性一节中讨论了一种简单方法，通过说明等式连接谓词间的统计相关性而收集多列统计信息，这一节将介绍优化器如何确定两个连接表中的父表（如果有的话）。鉴别优化器是否会检测一个父表，如果会的话，哪一个是父表，这对于了解何时进行列组统计信息的收集才是有效的以及在哪个表上收集是非常有用的。

优化器在一组连接两表的谓词中标识父表，这个表至少是一个连接谓词的父表，而且不是任何其他连接谓词的子表。一个连接谓词的父表被确定为在谓词列中具有较多不同值的表。例如，在 SHOW_LISTINGS.SHOW_ID = RATINGS.SHOW_ID 连接谓词中，如果 SHOW_LISTINGS.SHOW_ID 的 COLCARD 统计信息比 RATINGS.SHOW_ID 的 COLCARD 统计信息多，那么 SHOW_LISTINGS 就是这个连接谓词的父表，RATINGS 则是子表。

此外，优化器还尝试使用范围统计信息（HIGH2KEY 和 LOW2KEY）来验证父表，方法是确保子表中的值集是父表的子集。例如，在连接谓词 SHOW_LISTINGS.SHOW_ID = RATINGS.SHOW_ID 中，如果符合以下条件：

SHOW_LISTINGS.SHOW_ID 的 COLCARD 统计信息多于 RATINGS.SHOW_ID 的统计信息，并且
SHOW_LISTINGS.SHOW_ID 的 HIGH2KEY 多于或等于 RATINGS.SHOW_ID 的 HIGH2KEY，并且
RATINGS.SHOW_ID 的 LOW2KEY 少于或等于 RATINGS.SHOW_ID 的 LOW2KEY

那么 SHOW_LISTINGS 是这个连接谓词的父表，而 RATINGS 则是子表。

示例 B.1

假设以下一组谓词：

P1: SHOW_LISTINGS.SHOW_ID = RATINGS.SHOW_ID
            P2: SHOW_LISTINGS.CHANNEL_ID = RATINGS.CHANNEL_ID
            P3: SHOW_LISTINGS.STATION_ID = RATINGS.STATION_ID

在 P1 中，假设 COLCARD(SHOW_LISTINGS.SHOW_ID) > COLCARD(RATINGS.SHOW_ID)。

在 P2 中，假设 COLCARD(SHOW_LISTINGS.CHANNEL _ID) > COLCARD(RATINGS.CHANNEL_ID)。

在 P3 中，假设 COLCARD(SHOW_LISTINGS.STATION_ID) > COLCARD(RATINGS.STATION_ID)。

优化器将 SHOW_LISTINGS 表标识为所有三个谓词的父表，所以它同样将 SHOW_LISTINGS 标识为连接的父表。因此优化器使用 SHOW_LISTINGS 表中的列（SHOW_ID，CHANNEL_ID 和 STATION_ID）上可用的多列统计信息。

示例 B.2

使用示例 B.1 中的谓词：

在 P1 中，假设 COLCARD(SHOW_LISTINGS.SHOW_ID) > COLCARD(RATINGS.SHOW_ID)。

在 P2 中，假设 COLCARD(SHOW_LISTINGS.CHANNEL _ID) > COLCARD(RATINGS.CHANNEL_ID)。

在 P3 中，假设 COLCARD(SHOW_LISTINGS.STATION_ID) = COLCARD(RATINGS.STATION_ID)。

优化器在谓词 P1 和 P2 中将 SHOW_LISTINGS 表标识为父表，在谓词 P3 中既不是子表，也不是父表。所以优化器也将 SHOW_LISTINGS 标识为连接的父表。因此，优化器使用 SHOW_LISTINGS 表中的列（SHOW_ID，CHANNEL_ID 和 STATION_ID）上可用的多列统计信息。

示例 B.3

使用示例 B.1 中的谓词：

在 P1 中，假设 COLCARD(SHOW_LISTINGS.SHOW_ID) > COLCARD(RATINGS.SHOW_ID)。

在 P2 中，假设 COLCARD(SHOW_LISTINGS.CHANNEL _ID) < COLCARD(RATINGS.CHANNEL_ID)。

在 P3 中，假设 COLCARD(SHOW_LISTINGS.STATION_ID) = COLCARD(RATINGS.STATION_ID)。

优化器在谓词 P1 中将 SHOW_LISTINGS 表标识为父表，在谓词 P2 中将其标识为子表，在谓词 P3 中既不是子表也不是父表。所以，并不将 SHOW_LISTINGS 标识为连接中的父表（也不将 RATINGS 标识为连接的父表）。因此，优化器并不会检测这三个连接的谓词间的统计相关性。

数据库分区特性

在一个 DPF 环境中，如果表跨多个数据库分区而被分区，在下列条件满足的情况下，只能使用范围统计信息：

两个表在相同的列中被分区
表是并置的，就是说，它们处于同一节点组
连接谓词引用所有的分区键列
在相同的节点收集统计信息

如果不能够使用范围统计信息，那么优化器只能使用 COLCARD 统计信息来确定连接中的父表。在 DPF 环境中，在单个节点中收集统计信息将会导致在 COLCARD 统计信息出现错误。因此，确定父表时，优化器允许 COLCARD 统计信息中存在一些偏差（1%）。然而，这种偏差是以多列统计信息为基础的，因此如果该连接不是中立的，并且连接中没有很明显的父表，那么在 DPF 中应该使用收集列组统计信息的简单方法。

示例 B.4

1. 确定两个表是否在相同列上被分区

除非两个表的数据分布相同，否则不能使用范围统计信息（HIGH2KEY 和 LOW2KEY）。因此，如果表不是在相同列被分区，优化器将无法推断出数据在两个表中的分布相同。

假设有两个表，T1 和 T2。T1 在多个数据库分区中被分区，在列（C1 和 C2）中具有分区键。同样，T2 也进行了分区，其分区键在列（C2 和 C3）中。

例 1.1 使用下列谓词连接 T1 和 T2：

P1:  T1.C1=T2.C2
            P2:  T1.C2=T2.C3

这两个表被认为是在相同的列进行了分区，因为连接谓词按照与其各自的分区键相同的次序应用于列中。

例 1.2 使用下列谓词：

P1:  T1.C1=T2.C3
            P2:  T1.C2=T2.C2

谓词没有按照与其各自的分区键相同的次序应用于列中，所以这两个表被认为不是在相同列上分区的。

例 1.3 使用下列谓词：

P1: T1.C1=T2.C1
            P2: T1.C2=T2.C2
            P3: T1.C3=T2.C3

这些表被认为不是在相同列分区的。

例 1.4 来看一下和示例 1.3 使用相同谓词的情形，但是假设 T2 是在列（C1，C2 和 C3）中分区的。

尽管表 T1 是在相同列分区的（T2 分区键的两个主要列），由于 T2 在 C3 中进一步分区，与只在（C1 和 C2）中分区相比，这将导致数据分布不同。因此，这两个表被认为是在不同列进行分区的。

2. 确定这两个表是否是并置的

假设有两个表，T1 和 T2，T1 属于节点组 N0 而 T2 属于节点组 N1，其中 N0 包含分区数 0 和 1 ，N1 包含分区数 1 和 2 。由于这两个表不在同一个节点组，将认为它们不是并置的。

3. 确定连接的谓词是否引用所有的分区键列

假设有两个表，T1 和 T2。T1 在多个数据库分区中被分区，分区键在列（C1 和 C2）。同样，T2 也被分区，分区键在列（C2 和 C3）。

3.1 使用这些谓词来连接 T1 和 T2：

P1:  T1.C1=T2.C2
            P2:  T1.C2=T2.C3

这两个谓词都包含了所有的分区键列。

例 3.2 如果在示例 3.1 谓词中添加第三个谓词 P3，T1.C3=T2.C1，那么这三个谓词合起来仍然包含两个表中的所有分区键列。然而，如果仅使用 P1 和 P3，所有这三个谓词并不包含分区键列。

例 3.3如果在示例 3.1 谓词中添加第三个谓词 P3，T1.C3=T2.C3，那么这三个谓词合起来仍然包含两个表中的所有分区键列。然而，如果只使用P1 和 P3，那么只包含了 T2 的分区键列而没有包含 T1 的，所以条件并不符合。

完全限定的惟一索引

如果在上述条件的基础上，没有一个表被指定为连接的父表，优化器将进一步检查一个完全限定的惟一索引。如果连接谓词完全限定一个惟一索引，优化器使用惟一索引的 FULLKEYCARD 统计信息来检测和说明相等连接谓词之间的统计相关性。

中立连接

如果未在两个表间的任一等式连接谓词中标识出父表，优化器将把这个连接标识为中立（neutral）。如果两个列的 COLCARD 相同，并且它们包含的值的范围也是相同的（HIGH2KEY 和 LOW2KEY 统计信息是等同的），则不对这个等式连接谓词标识父表。

在 DB2 V8 的 FixPaks 13 及更早版本中，如果是一个中立的 NPK 连接，优化器就不会为两个表的连接说明一组等式连接谓词间的统计相关性。在 V8 FixPak 14 和 DB2 9 中，优化器的统计相关性检测得到了扩展，可以对中立的 NPK 连接进行统计相关性说明。

两个表引用相同的基表，这种自连接是中立连接的特例。在 V8.2 中，优化器开始为这种特例说明统计相关性。

示例 B.5：在父表中收集列组统计信息

这个例子主要关注表 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.

1. 使用 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

2. 查看连接谓词引用的列的列统计信息。如下查询为两个连接谓词中引用的表 ORG 和 STAFF 中的列从 DB2 目录表中检索有趣的列统计信息：

SELECT SUBSTR(COLNAME,1,20) AS COLNAME, COLCARD,
            SUBSTR(HIGH2KEY,1,10) AS HIGH2KEY,
            SUBSTR(LOW2KEY,1,10) AS LOW2KEY
            FROM SYSSTAT.COLUMNS
            WHERE TABNAME IN ('ORG', 'STAFF')
            AND COLNAME IN ('MANAGER', 'DEPTNUMB', 'ID', 'DEPT')
            ORDER BY TABNAME, COLNAME;

表 B.5 列统计信息

COLNAME	COLCARD	HIGH2KEY	LOW2KEY
DEPTNUMB	8	66	15
MANAGER	8	270	30
DEPT	8	66	15
ID	35	340	20

3. 计算基数估计值。

等式连接谓词的过滤因子的估计值计算如下：

1/max(colcard_LHS,colcard_RHS).

其中 LHS 在连接谓词的左边，RHS 在连接谓词的右边。本例中有以下两个连接谓词：

P1: org.manager=staff.id
            P2: org.deptnumb=staff.dept

以及过滤因子（ff），P1 和 P2 计算如下：

ff(P1) = 1 / max (8,35) = 1/35 = 0.0285714
            ff(P2) = 1/max(8,8) = 1/8 = 0.125

使用过滤因子以及两个表的基数估计值，连接基数计算如下：

JoinCard  = Cardinality(ORG) * cardinality(STAFF) * ff(P1) * ff(P2)
            = 35 * 8 * 0.0285714 * 0.125
            = 1

4. 在连接中标识父表。

在表 B.5 中，列统计信息显示 STAFF 是连接的父表，因为符合以下条件：

对于谓词 org.manager=staff.id:

colcard(MANAGER)  < colcard(ID)
            high2key(MANAGER) < high2key(ID)
            low2key(MANAGER) > low2key(ID)

因此，这个谓词的父表是 STAFF。

对于谓词 org.deptnumb=staff.dept，colcard、high2key 和 low2key 统计信息是等同的。因此，这个谓词不存在父表，它应被视为“中立”。

5. 在父表中收集列组统计信息。

在步骤 4 中，STAFF 被标识为连接的父表，ORG 被标识为连接的子表，所以应在 STAFF 表的列（ID，DEPT）中收集列组统计信息：

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

使用这两个列的列组统计信息，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

示例 B.6 中立连接

考虑 T1 和 T2 这两个表的连接，使用以下谓词：

            P1: T1.C1 = T2.C1
            P2: T1.C2 = T2.C2
            P3: T1.C3 = T2.C3

假设这两个表具有以下的统计信息：

表 B.6 中立连接统计信息

TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY	TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY
T1	C1	100	99	2	T2	C1	100	99	2
T1	C2	10	1999	1900	T2	C2	10	1999	1900
T1	C3	5	'Y'	'B'	T2	C3	5	'Y'	'B'

所有的谓词被看作是中立的，这是由于两个表的 stats 是相等的。因此，在 V8 FixPak 14 和 DB2 9 中，应该在任一个表（不要求两个表）的列（C1、C2 和 C3）中收集列组统计信息，这样，优化器就可以检测这些连接谓词的统计相关性。

示例 B.7

使用和示例 B.6 相同的谓词，假设表具有如下统计信息：

表 B.7 确定父表

TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY	TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY
T1	C1	100	99	2	T2	C1	10	99	2
T1	C2	10	1999	1900	T2	C2	10	1999	1900
T1	C3	5	'Y'	'B'	T2	C3	5	'Y'	'B'

在这个场景中，谓词 P2 和 P3 看作是中立的，这是由于两个表的列 C2 和 C3 的统计信息是等同的。然而，C1 的统计信息显示 T1 是 P1 的父表，这是因为 T1.C1 的 COLCARD 比 T2.C1 的 COLCARD 大，并且两个列具有等同的 HIGH2KEY 和 LOW2KEYare 统计信息。因此，应该在 T1 的列（C1、C2 和 C3）中收集列组统计信息，这样，优化器就可以检测连接谓词间的统计相关性。

示例 B.8

使用和示例 B.6 相同的谓词，假设表具有下列统计信息：

表 B.8 统计信息

TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY	TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY
T1	C1	100	99	2	T2	C1	10	999	2
T1	C2	10	1999	1900	T2	C2	10	1999	1900
T1	C3	5	'Y'	'B'	T2	C3	5	'Y'	'B'

在这个场景中，谓词 P2 和 P3 被表示为中立的，这是由于两个表的 C2 和 C3 列的统计信息是等同的。然而，C1 的统计信息表明 T1 和 T2 都不是父表，它也不是中立的，因为 T1.C1 具有一个更高的 COLCARD 统计信息，而 T2.C1 具有一个更高的统计信息。因此，应该在 T1 或 T2 的列（C2 和 C3）中收集列组统计信息，这样优化器就可以检测连接谓词 P2 和 P3 之间的统计相关性。

示例 B.9 使用 DPF 和范围统计信息

考虑 T1 和 T2 这两个表的连接，它们在多个数据库分区上被分区，并且是并置的，统计信息是在相同的节点手机的，分区键在列（C1 和 C2）上，使用如下谓词进行连接：

            P1: T1.C1 = T2.C1
            P2: T1.C2 = T2.C2
            P3: T1.C3 = T2.C3

假设这两个表具有如下统计信息：

表 B.9

TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY	TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY
T1	C1	100	99	2	T2	C1	10	999	2
T1	C2	10	1999	1900	T2	C2	10	1999	1900
T1	C3	5	'Y'	'B'	T2	C3	5	'Y'	'B'

这个连接限定 DPF 条件，所以优化器可以使用范围统计信息来标识父表。在这个场景中，谓词 P2 和 P3 被表示为中立，这是由于两个表的 C2 和 C3 列的统计信息是等同的。然而，C1 的统计信息表明 T1 和 T2 都不是父表，也不把它看作是中立的，这是因为 T1.C1 具有一个更高的 COLCARD，而 T2.C1 具有一个更高的 HIGH2KEY 统计信息。因此，应该在 T1 或 T2 的列（C2 和 C3）上收集列组统计信息，这样优化器就可以检测连接谓词 P2 和 P3 间的统计相关性。

示例 B.10 无法使用 DPF 和范围统计信息

假设与 B.9 相同的场景，但是 T1 的分区键是在列（C1 和 C2）中，T2 的分区键在列（C2 和 C1）中。这个连接不满足第一个 DPF 条件，因为这两个表被认为不是在相同的列分区的；连接谓词 T1.C1=T2.C1 在 T1 的分区键中引用了第一个列，但在 T2 分区键中引用了第二个列。因此，优化器不能使用范围统计信息来标识父表，而只能使用 COLCARD 统计信息来确定父表。只在 COLCARD 的基础上，连接被认为是中立的。

示例 B.10.1 无法使用 DPF 和范围统计信息

假设与 B.10 相同的场景，但具有下列统计信息：

表 B.10

TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY	TABLENAME	COLNAME	COLCARD	HIGH2KEY	LOW2KEY
T1	C1	100	99	2	T2	C1	98	999	2
T1	C2	10	1999	1900	T2	C2	11	1999	1900
T1	C3	5	'Y'	'B'	T2	C3	5	'Y'	'B'

在 COLCARD 统计信息基础上，T1 是谓词 P1 的父表，而 T2 是谓词 P2 的父表。由于 COLCARD 统计信息被关闭，因此多列统计信息的偏差将降至 1% 以内，这是优化器在 DPF 环境中的容错值。在这里它可以使用简单方法，并且应在两个表的所有三个列中收集列组统计信息。之后，如果优化器没有计算出一个不同的基数估计值，那么说明并未达到 1% 的偏差。

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

关注我们