[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
其中