HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

CBO全表扫描代价计算公式推导(6)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-08-01 15:03:32

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

3.4.20.           OR

 

当引入OR以后,情况变得更加复杂。它最少产生以下2方面的影响:1、选择性的变化;2ORAND的优先级不同,因此要影响到过滤顺序。

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.                    ORAND混合

然后我们再看在ORAND的混合表达式中的计算。我之前说过,ORAND的优先级不同,AND的优先级高于OR,即在没有()限制的情况下,表达式会选处理AND再处理OR。这一点很快就让我们遇到一个困扰:在存在不同优先级的情况下,处理的最小单位不是单个字段,而是一个子句。例如,这个过滤式存在2个优先级:(a=:v1 or b<:v2) and (c=:v3 or d in (:d1,:d2))。在对第一优先级(小括号中的2or子句)处理时,处理单位是字段,可以按照我们之前的方法计算,但是,对于第二优先级,最小单位就是这个2个子句了。于是,我们首先要解决的问题是如何计算一个子句的COLTYPFACCOLSEL

 

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

其中SUBEXPTYPFACSUBEXPSEL可以为子句或者字段的TYPFACSELECTIVITY

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的默认优先级是高于ANDOR

4.   总结

由此,COST_CPU的公式可以演化为:

公式22COST_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

其中