CBO全表扫描代价计算公式推导(5)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
3.4.15. 过滤表达式匹配符（LIKE）
有了之前的推导经验和公式积累，在推导LIKE匹配符时，过程比较顺利。我直接对混合数据类型+混合NDV（实际上，由查询计划中Cardinality的计算的推导得出的结论，对绑定变量使用LIKE匹配符和>、<匹配符的选择性 (1/NDV) 是一样，都是固定为1/20，从下面的结果看，也是如此）情况进行推导：
A:160
B:80
C:100
D:200
E:400
A:VARCHAR2
B:NUMBER
C:DATE
D:DATE
E:NUMBER
EFFQRYCOLNUM
0
FLTCOLNUM
FILTER
COST_CPU
CPU_A
TYPFAC
CACULATED
1
a like :v1
257121440
7121440
250000000
250
2
a like :v1 and b like :v3
287121440
7121440
280000000
280
3
a like :v1 and b like :v2 and c like :v3
307996440
7121440
300875000
300.875
4
a like :v1 and b like :v2 and c like :v3 d like :v4
328040190
7121440
320918750
320.91875
从这些数据最终找到的规则是：LIKE匹配的选择性(1/NDV)为固定值1/20，但是它会使字段的COLTYPEFAC加50。例如上面第三条的计算式为：
130+3*20+100*1+200*(1/20)+350*(1/20)^2 = 300.875
再考虑有效查询字段：
EFFQRYCOLNUM
1
FLTCOLNUM
FILTER
COST_CPU
CPU_A
TYPFAC
CACULATED
1
a like :v1
258121440
7121440
251000000
251
2
a like :v1 and b like :v3
287171440
7121440
280050000
280.05
3
a like :v1 and b like :v2 and c like :v3
307998940
7121440
300877500
300.8775
4
a like :v1 and b like :v2 and c like :v3 d like :v4
328040310
7121440
320918870
320.91887
EFFQRYCOLNUM
2
FLTCOLNUM
FILTER
COST_CPU
CPU_A
TYPFAC
CACULATED
1
a like :v1
287221440
7121440
280100000
280.1
2
a like :v1 and b like :v3
308001440
7121440
300880000
300.88
3
a like :v1 and b like :v2 and c like :v3
328040430
7121440
320918990
320.91899
4
a like :v1 and b like :v2 and c like :v3 d like :v4
259121440
7121440
252000000
252
结合上面的规则，这个结果完全符合公式18。
3.4.16. 其他过滤表达式匹配符（<>、IN、NOT IN）
由我在查询计划中Cardinality的计算一文中的结论，各个匹配符在使用绑定变量时选择性(1/NDV)为：
<>: 1-1/NDV
IN: IN中变量数*(1/NDV)
NOT IN: (1-1/NDV)^(NOT IN中变量数)
结合测试数据和之前推导出的公式，不难得出这几种匹配符的公式。
3.4.16.1. <>匹配符
这是部分<>匹配符的测试数据：
A:160
B:80
C:100
D:200
E:400
A:VARCHAR2
B:NUMBER
C:DATE
D:DATE
E:NUMBER
EFFQRYCOLNUM
0
FLTCOLNUM
FILTER
COST_CPU
CPU_A
CPU_B
TYPFAC
CACULATED
1
a <> :v1
207121440
7121440
200000000
200
200.00
EFFQRYCOLNUM
2
FLTCOLNUM
FILTER
COST_CPU
CPU_A
CPU_B
TYPFAC
CACULATED
1
a <> :v1
246871440
7121440
239750000
239.75
239.75
2
a <> :v1 and b <> :v3
415437060
7121440
408315620
408.31562
408.31562
3
a <> :v1 and b <> :v2 and c <> :v3
729442978
7121440
722321538
722.321538
722.3215375
4
a <> :v1 and b <> :v2 and c <> :v3 d <> :v4
1040703111
7121440
1033581671
1033.581671
1033.581671
<>匹配符的选择性的计算是1-1/NDV，其中NDV为字段的唯一值数。上述数据完全符合公式18。例如第三条数据的计算：
130+3*20+50*1+150*(1-1/160)+300*(1-1/160)*(1-1/80)+2*20*MAX(1,ROUND((1-1/160)*(1-1/80)*(1-1/100)*1000000,0))/1000000 = 722.3215375
3.4.16.2. IN匹配符
当匹配符为IN时，除了选择性上的变化，还有过滤字段系数上有变化：
A:160
B:80
C:100
D:200
E:400
A:VARCHAR2
B:NUMBER
C:DATE
D:DATE
E:NUMBER
EFFQRYCOLNUM
0
FLTCOLNUM
FILTER
COST_CPU
CPU_A
CPU_B
TYPFAC
CACULATED
1
a in (:a1)
207121440
7121440
200000000
200
200.00
1
a in (:a1, :a2)
256808940
7121440
249687500
249.6875
249.6875
1
a in (:a1, :a2, :a3)
306185893
7121440
299064453
299.064453
299.0644531
EFFQRYCOLNUM
2
FLTCOLNUM
FILTER
COST_CPU