CBO全表扫描代价计算公式推导(6)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
3.4.20. OR
当引入OR以后，情况变得更加复杂。它最少产生以下2方面的影响：1、选择性的变化；2、OR与AND的优先级不同，因此要影响到过滤顺序。
3.4.20.1. 同一字段上多个OR
这一点相对比较简单。从逻辑上看，我们知道，在同一优先级内，同一字段的多个OR实际上就等于对这个字段进行了一次多变量的IN匹配。通过测试数据也能证明这一点：
EFFQRYCOLNUM
1
FILTER
COST_CPU
CPU_A
TYPFAC
CACULATED
a in (:a1, :a2)
257421440
7121440
250300000
250.3
a = :a1 or a = :a2
257421440
7121440
250300000
250.3
3.4.20.2. 不同字段上的OR
当对不同字段进行OR操作时，情况变得更加复杂。最重要的是，当与AND混合存在时，过滤字句中会形成多个优先级。不过，只要数据充分，还是能发现其中的奥秘的。我们先从纯OR表达式中找规律：
a varchar2(50), b number, c varchar2(50), d date, e date, f number,g nunber
EFFQRYCOLNUM
0
a:100;b:200;c:300;d:80;e:160;f:250;g:400
TABROWS
1000000
FILTER
COST_CPU
CPU_A
CPU_B
TYPFAC
b=:v2 or c=:v3 or d=:v4
366372690
7121440
359251250
359.25125
a=:v1 or b=:v2 or d=:v3 or d=:v4
415626434
7121440
408504994
408.504994
a=:v1 and b=:v2 or c=:v3 or d=:v4
366620196
7121440
359498756
359.498756
b>:v2 or c<:v3 or d>:v4
359746440
7121440
352625000
352.625
b<>:v2 or c<>:v3 or d<>:v4
267372690
7121440
260251250
260.25125
b like :v2 or c like :v3 or d like :v4
502371440
7121440
495250000
495.25
规律很快就可以找到：OR操作的选择性是（1-原选择性）。例如对a IN (:a1, :a2)进行OR操作，其选择性变为(1-2*1/100)。
3.4.20.3. OR与AND混合
然后我们再看在OR与AND的混合表达式中的计算。我之前说过，OR与AND的优先级不同，AND的优先级高于OR，即在没有()限制的情况下，表达式会选处理AND再处理OR。这一点很快就让我们遇到一个困扰：在存在不同优先级的情况下，处理的最小单位不是单个字段，而是一个子句。例如，这个过滤式存在2个优先级：(a=:v1 or b<:v2) and (c=:v3 or d in (:d1,:d2))。在对第一优先级（小括号中的2个or子句）处理时，处理单位是字段，可以按照我们之前的方法计算，但是，对于第二优先级，最小单位就是这个2个子句了。于是，我们首先要解决的问题是如何计算一个子句的COLTYPFAC和COLSEL。
a varchar2(50), b number, c varchar2(50), d date, e date, f number,g nunber
EFFQRYCOLNUM
0
a:100;b:200;c:300;d:80;e:160;f:250;g:400
TABROWS
1000000
FILTER
COST_CPU
CPU_A
CPU_B
TYPFAC
b > :v2 and c = :v3
247621440
7121440
240500000
240.5
b > :v2 or c = :v3
394621440
7121440
387500000
387.5
a like :v1 and b > :v2
284621440
7121440
277500000
277.5
a like :v1 or b > :v2
419621440
7121440
412500000
412.5
a <> :v1 and b = :v2 and c in (:c1, :c2)
297956440
7121440
290835000
290.835
a <> :v1 or b = :v2 or c in (:c1, :c2)
249609773
7121440
242488333
242.488333
a like :v1 and c = :v3 and d in (:d1, :d2)
267554148
7121440
260432708
260.432708
a like :v1 and (b > :v2 or c = :v3)
306996440
7121440
299875000
299.875
还是以测试数据为依据，最终得出对子句的计算：
- AND子句：
EXPTYPEFAC = MIN((COLTYPFAC1+ COLTYPFAC2*COLSEL1), (COLTYPFAC2+ COLTYPFAC1*COLSEL2)
EXPSEL = COLSEL1*COLSEL2
- OR子句：
EXPTYPEFAC = MIN((COLTYPFAC1+ COLTYPFAC2*(1-COLSEL1)), (COLTYPFAC2+ COLTYPFAC1*(1-COLSEL2))
EXPSEL = COLSEL1 + COLSEL2 - COLSEL1*COLSEL2
当单位为子句时，可对上述公式做扩展：
公式21：
- AND子句：
EXPTYPEFAC = MIN((SUBEXPTYPFAC1+ SUBEXPTYPFAC2*SUBEXPSEL1), (SUBEXPTYPFAC2+ SUBEXPTYPFAC1*SUBEXPSEL2)
EXPSEL = SUBEXPSEL1*SUBEXPSEL2
- OR子句：
EXPTYPEFAC = MIN((SUBEXPTYPFAC1+ SUBEXPTYPFAC2*(1-SUBEXPSEL1)), (SUBEXPTYPFAC2+ SUBEXPTYPFAC1*(1-SUBEXPSEL2))
EXPSEL = SUBEXPSEL1 + SUBEXPSEL2 - SUBEXPSEL1*SUBEXPSEL2
其中SUBEXPTYPFAC和SUBEXPSEL可以为子句或者字段的TYPFAC和SELECTIVITY。
3.4.21. NOT
从OR的经验，我们很容易推导NOT的情况：它使子句的选择性变为（1-EXPSEL）。测试数据也能证明：
EFFQRYCOLNUM:0
not (a=:v1 and b=:v2 and c=:v3)
247293107
7121440
240171667
240.171667
EFFQRYCOLNUM:1
b=:v1
327221440
7121440
320100000
320.1
EFFQRYCOLNUM:1
not (b=:v1)
347021440
7121440
339900000
339.9
EFFQRYCOLNUM:1
not (a like :v1) and not (b = :v2)
285616440
7121440
278495000
278.495
EFFQRYCOLNUM:1
(a like :v1) and (b = :v2)
284626440
7121440
277505000
277.505
EFFQRYCOLNUM:1
(a like :v1 or b = :v2)
420716440
7121440
413595000
413.595
EFFQRYCOLNUM:1
not (a like :v1 or b = :v2)
285616440
7121440
278495000
278.495
EFFQRYCOLNUM:0
(a like :v1 or b = :v2)
419621440
7121440
412500000
412.5
EFFQRYCOLNUM:0
not (a like :v1 or b = :v2)
284621440
7121440
277500000
277.5
因此过滤式中引入了NOT时，选择性取反，且符合公式22。要注意的是NOT的默认优先级是高于AND和OR的。
4. 总结
由此，COST_CPU的公式可以演化为：
公式22：COST_CPU = TYPFAC*TABROWS + 0.32*TABBLKS*TABSIZ + 4500*TABBLKS
其中
TYPFAC = ROUND((130 + MAXFLTCOLPOS*20 + EXPTYPEFAC1+ EXPTYPEFAC2* EXPSEL1 + 20*EFFQRYCOLNUM*MAX(1,ROUND(EXPTYPEFAC *TABROWS,0))/TABROWS)*TABROWS,0)/TABROWS
其中