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
A&E
289621440
7121440
282500000
这组数据发生了线性变化。我们可以得出一个这样的结论：多个AND过滤字段中，只有其中的最大位置会影响COST_CPU，其他字段位置不影响。基于这个结论，我们先下一个这样假设8：公式8、9中的FILCOLPOS就是过滤字段最大位置MAXFLTCOLPOS。但是，由于上面的数据中存在2个变数：MAXFLTCOLPOS和过滤字段数FLTCOLNUM，且FLTCOLNUM并不呈线性变化（有其它数据得出，在后面给出），不能直接通过解方程组的方法解出。那么我先看看基于这个假设，有没有办法解出关于FLTCOLNUM的非一次方程出来。
先由假设8得出新的公式
公式8-1：EFFCOLNUMS = EFFQRYCOLNUM + MAXFLTCOLPOS
公式10：TYPFAC = EFFCOLNUMS + 19*MAXFLTCOLPOS + 180
3.4.7. 过滤字段数
过滤字段数在整个公式中是最特殊。无法列出简单方程式，最终还是通过观察——假设公式——反证的方法找出答案。先看它对COST_CPU的影响：
（所有字段数据类型相同，TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5; MAXFILPOS: 5）
FLTCOLNUM
COST_CPU
CPU_B
CPU_A
1
287121440
7121440
280000000
2
289621440
7121440
282500000
3
289746440
7121440
282625000
4
289752690
7121440
282631250
5
289753003
7121440
282631563
看到这组数据的变化不是线性的。这让我很难列方程式。但是，可以通过其他方法看看是否有规律可循，先根据公式5解出各个TYPFAC来：
FLTCOLNUM
COST_CPU
CPU_B
CPU_A
TYPFAC
1
287121440
7121440
280000000
280
2.5
2
289621440
7121440
282500000
282.5
0.125
3
289746440
7121440
282625000
282.625
0.00625
4
289752690
7121440
282631250
282.63125
0.000313
5
289753003
7121440
282631563
282.63156
再将计算出的TYPFAC前后相减，得出另外一组数据。哈哈，这是一组有规律的数据：前后相差20倍！2.5/20 = 0.125; 0.125/20 = 0.00625; 0.00625/20 = 0.0003125 ~= 0.000313。按照这样的规律，推测出这样一个公式：
公式10-1：TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(20^(1 – FLTCOLNUM) + 20^(2 – FLTCOLNUM) + … + 20^( FLTCOLNUM- FLTCOLNUM))
= 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(1/20)^(FLTCOLNUM-1) + 50*(1/20)^(FLTCOLNUM-2) + … + 50(1/20)^(FLTCOLNUM- FLTCOLNUM)
= 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 50*(1/20)^0 + 50*(1/20)^1 + … + 50*(1/20)^(FLTCOLNUM-1)
通过更多的其它数据，证明了这个公式是成立的，说明之前假设8也是成立的。
3.4.8. 过滤字段数据类型
在前面的推导过程中，为了消除数据类型的影响，我将所有字段都设置为了VARCHAR2(50)。现在，可以开始推导数据类型的影响。
首先，通过数据证明数据类型对有效查询字段没有影响（数据不再列出）。
下面要推导的是过滤字段数据类型对公式的影响。由于数据类型之间的变化不是线性的，我们假设它是一个CACE ... WHEN的关系：
限制将数据类型改为CHAR，发现结果和VARCHAR2一直
再把所有数据类型改为NUMBER，
（所有字段数据类型相同，TABROWS: 1000000; TABBLKS: 1000; EFFCOLNUMS: 5; MAXFILPOS: 5）
FLTCOLNUM
COST_CPU
CPU_B
CPU_A
TYPFAC
1
387121440
7121440
380000000
380
7.5
2
394621440
7121440
387500000
387.5
0.375
3
394996440
7121440
387875000
387.875
0.01875
4
395015190
7121440
387893750
387.89375
0.000938
5
395016128
7121440
387894688
387.89469
通过数据对比，不难发现公式10-1演变成了：
公式10-2：TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 150*(1/20)^0 + 150*(1/20)^1 + … + 150*(1/20)^(FLTCOLNUM-1)
然后再推导DATE类型
FLTCOLNUM
COST_CPU
CPU_B
CPU_A
TYPFAC
1
537121440
7121440
530000000
530
15
2
552121440
7121440
545000000
545
0.75
3
552871440
7121440
545750000
545.75
0.0375
4
552908940
7121440
545787500
545.7875
0.001875
5
552910815
7121440
545789375
545.78938
公式演变为：
公式10-3：TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + 300*(1/20)^0 + 300*(1/20)^1 + … + 300*(1/20)^(FLTCOLNUM-1)
其它数据类型就不再推导了。由此，可以得出新的计算公式：
公式11：TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + COLTYPEFAC*(1/20)^0 + COLTYPEFAC*(1/20)^1 + … + COLTYPEFAC*(1/20)^(FLTCOLNUM-1)
数据类型
COLTYPEFAC
CHAR
50
VARCHAR
50
NUMBER
150
DATE
300
3.4.9. 段数长度
再观察字段长度是否有影响，经测试数据证实，长度不影响COST_CPU。
3.4.10. 过滤字段计算顺序
在前面的推导中，表中所有字段的数据类型都是统一的，但是我们也看到，不同数据类型是有不同COLTYPEFAC系数的。那么，当存在混合的数据类型结果有如何呢？还是从特殊情况入手：最大位置的过滤字段设置为number，其他字段设置为date（之所以这样设，也是我故意安排，如果将两个数据类型反过来，结果就很难推了，在后面的推导中会说明这个问题），观察变化：
FLTCOLNUM
COST_CPU
CPU_B
CPU_A
TYPFAC
1
387121440
7121440
380000000
380
15
2
402121440
7121440
395000000
395
0.75
3
402871440
7121440
395750000
395.75
0.0375
4
402908940
7121440
395787500
395.7875
0.001875
5
402910815
7121440
395789375
395.78938
可以看到，COLTYPEFAC的系数变化是基于DATE的，尝试完全按照DATE的COLTYPEFAC计算出来，比较数据：
FLTCOLNUM
TYPFAC_CAL
1
380
2
387.5
0.375
3
387.875
0.01875
4
387.89375
0.0009375
5
387.8946875
发现数据变化也是呈之前的幂排列的方式变化的，其基数正是DATE与NUMBER的COLTYPEFAC之差（通过类似方法对DATE与VARCHAR2、NUMBER与VARCHAR2也能得出同样结果）。因此，变化公式11：
公式12：TYPFAC = 130 + EFFCOLNUMS + 19*MAXFLTCOLPOS + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
其中，过滤字段的处理顺序将在3.4.13中再次确认。
根据这个公式，再对测试数据进行计算，结果都吻合。