CBO全表扫描代价计算公式推导(3)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
3.4.4. 过滤字段位置
这一点实际上是不能单独考虑的，因为如果存在多个过滤字段，每个过滤字段都有一个字段位置。我们这先考虑一个过滤字段的情况。
（select /*+full(a)*/a, b, c, d, e from T_PEEKING11 a where col < :v2;）
（TABROWS: 1000000; TABBLKS: 1000; QRYCOLNUMS: 5）
FLTCOLPOS
COST_CPU
CPU_B
cpu_A
1
211121440
7121440
204000000
2
230121440
7121440
223000000
3
249121440
7121440
242000000
4
268121440
7121440
261000000
很好，变化是线性的，且符合假设4的情况。因此我们需要推导FLTCOLPOS对TYPFAC的方程式。在公式6中，存在2个不确定因子，因此我们要解一个4元一次方程式组，而这里我们只变换了公式其中一个数，因此只能得出2个方程式来，还不足以求解：
(X1*1+Z1)*5 + (X2*1+Z2) = 204000000/1000000 … … (1)
(X1*2+Z1)*5 + (X2*2+Z2) = 223000000/1000000 … … (2)
再变化公式中的一个系数——QRYCOLNUMS：
（select /*+full(a)*/a, b, c, d from T_PEEKING11 a where col < :v2;）
（TABROWS: 1000000; TABBLKS: 1000; QRYCOLNUMS: 4）
FLTCOLPOS
COST_CPU
CPU_B
cpu_A
1
210121440
7121440
203000000
2
229121440
7121440
222000000
3
248121440
7121440
241000000
4
267121440
7121440
260000000
得出方程组中的另外2个方程式
(X1*1+Z1)*4 + (X2*1+Z2) = 203000000/1000000 … … (3)
(X1*2+Z1)*4 + (X2*2+Z2) = 222000000/1000000 … … (4)
(2) – (1), (4) – (3) =>
X1*5 + X2 = 19
X1*4 + X2 = 19
=>
X1 = 0
=>
X2 = 19
再解二元一次方程组：
Z1*5 + 19 + Z2 = 204
Z1*4 + 19 + Z2 = 203
=>
Z1 = 1
Z2 = 180
得出新的TYPFAC的公式：
公式7：TYPFAC = QRYCOLNUMS + 19*FLTCOLPOS + 180
3.4.5. 有效字段数
这个概念可能你一时无法理解。这是我在测试过程中出现违反之前公式的特例中发现的另外一个影响因子。先看以下的测试数据：
（select /*+full(a)*/a from T_PEEKING11 a where c < :v2;）
COST_CPU
CPU_B
cpu_A
247121440
7121440
240000000
（select /*+full(a)*/a, b from T_PEEKING11 a where c < :v2;）
COST_CPU
CPU_B
cpu_A
247121440
7121440
240000000
（select /*+full(a)*/a, b, d from T_PEEKING11 a where c < :v2;）
COST_CPU
CPU_B
cpu_A
248121440
7121440
241000000
第一份数据查询了1个字段，第二份数据查询了2个字段，但它们的结果却是相同的！而第三份数据是3个字段，它的结果与前面2份又不相同！这样一个结果最初很让我困惑，似乎我们之前的公式4、6不成立。而且当我将三份数据代入公式7发现结果不吻合：
1+19*3+180 = 238 != 240000000/1000000
2+19*3+180 = 239 != 240000000/1000000
3+19*3+180 = 240 != 241000000/1000000
OK！似乎过程中出现了偏差，抑或是我们的某个假设有错误。让我们再仔细观察一下这些特例数据，看看是否有规律可循。
先看第一、二份数据——他们的查询字段数变了，但是COST_CPU并没有变化，这里我留意到了它们之间的一个共性：查询字段的位置都小于过滤字段的位置（a：1；b：2；c：3）！那我们是否可以得出一个这样的假设5：位置小于或等于过滤字段位置的查询字段都不计算在有效的查询字段数内。通过多组数据，证明了该假设是成立的（无法找到特例，具体数据不再列举）。
那么，公式4中的QEYCOLNUMS值是从哪来的呢？先依据公式7，算出第1、2份数据中的QEYCOLNUMS：
240000000/1000000 – 180 – 19*3 = 3
3，这个数字意味这什么呢？我们发现过滤字段位置FLTCOLPOS也是为3，难道是巧合（或许我的这个发现是巧合J）。那我们可再假设一个命题，假设6：查询字段都不是有效字段时（或者说查询字段的位置都小于等于过滤字段），查询字段数（我们这里改称为有效字段数）为查询字段位置（即查询字段及其之前字段数）。同样，这个假设我也通过一组数据证明了（无法找到特例）。
但是，这两个假设的成立还不足以解释第三份数，按照公式7，其QRYCOLNUMS = 241000000/1000000 – 180 – 19*3 = 4，不等于过滤字段位置3。不用急，按照假设5，a, b两个字段不能计算在有效字段内，但是d不是假设5的范围之内的字段，如果将它考虑成有效字段，再加上假设6，由过滤字段的有效字段数为3，得出(3+1)=4。我们可以得出另外一个假设7：位置大于过滤字段位置的查询字段都计算在有效的查询字段数内。通过多组数据，证明了该假设是成立的。
OK，过滤字段位置的影响就这些吗？让我们再看下面一组测试数据：
select /*+full(a)*/X from T_PEEKING11 a where c < :v3;
X
COST_CPU
CPU_B
CPU_A
d
512873940
7121440
505752500
e
512876440
7121440
505755000
f
512878940
7121440
505757500
g
512881440
7121440
505760000
d, e
512876440
7121440
505755000
d, f
512878940
7121440
505757500
f, g
512881440
7121440
505760000
d, g ,h
512883940
7121440
505762500
由这组数据计算出来的EFFCOLNUMS，我们不难发现规律：有效字段数由位置大于过滤字段位置的查询字段中的最大位置决定。这也可以成为我们的一个假设8，并且同样用多组其它数据可以证明它。
由以上假设，可以得出：
公式8：EFFCOLNUMS = EFFQRYCOLNUM + FLTCOLPOS
EFFCOLNUMS：有效字段数
FLTCOLPOS：过滤字段位置
EFFQRYCOLNUM：有效查询字段。如果查询字段中存在位置大于过滤字段位置的字段，则为查询字段最大位置与过滤字段位置之差，否则为0。
公式7中QRYCOLNUMS也变为EFFCOLNUMS
公式9：TYPFAC = EFFCOLNUMS + 19*FLTCOLPOS + 180
3.4.6. 过滤字段最大位置
注：对多个过滤字段的情况中，我们先推导只有AND的情况，最后在引入OR进行推导。
完成上述推导后，我本来应该推导过滤字段数的影响公式的。但是，测试数据的特殊变化让我留意到了多过滤条件下的另外一个影响因素：过滤字段最大位置。
先看这一组数据：2个过滤字段，其中位置最大字段不变
（所有字段数据类型相同，TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5）
COLA
COLB
columns
COST_CPU
CPU_B
cpu_A
1
5
A&E
289621440
7121440
282500000
2
5
B&E
289621440
7121440
282500000
3
5
C&E
289621440
7121440
282500000
4
5
D&E
289621440
7121440
282500000
可以发现，小于最大字段位置的其他字段的位置变化不会影响结果。
再看另外一组数据：2个过滤字段，其中字段最大位置变化
（所有字段数据类型相同，TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5）
COLA
COLB
columns
COST_CPU
CPU_B
cpu_A
1
2
A&B
229771440
7121440
222650000
1
3
A&C
249721440
7121440
242600000
1
4
A&D
269671440
7121440
262550000
1
5