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

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-08-01 15:03:33

分享到 

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 CPU_A CPU_B TYPFAC CACULATED   1 a in (:a1, :a2) 257308940 7121440 250187500 250.1875 250.1875   2 a in (:a1, :a2, :a3) and b in (:b1, :b2) 331794497 7121440 324673057 324.673057 324.6730569   3 a in (:a1, :a2) and b in (:b1) and c in (:c1, :c2, :c3, :c4) 298868886 7121440 291747446 291.747446 291.7474462   4 a in (:a1, :a2, :a3) and b in (:b1, :b2) and c in (:c1, :c2) and d in (:d1, :d2, :d3, :d4) 372066787 7121440 364945347 364.945347 364.9453465                   由数据规律，最终还是发现了过滤字段系数COLTYPFAC的变化规则： COLTYPFAC_NEW = COLTYPEFAC_ORI*(1-1/NDV)^0+COLTYPEFAC_ORI*(1-1/NDV)^1+…+COLTYPEFAC_ORI*(1-1/NDV)^(INNUM-1) 其中INNUM为IN中变量个数。 此外，要注意的是，对相同数据类型的选择性排序时，是以(1-1/NDV)排序，而非(1/NDV)。（真正规则会在后面找到）。 上面第三条数据的计算为： 130+3*20+(50*(1-1/160)^0+50*(1-1/160)^1)*1+(150*(1-1/100)^0)*(2*1/160)+(300*(1-1/100)^0+300*(1-1/100)^1+300*(1-1/100)^2+300*(1-1/100)^3)*(2*1/160)*(1*1/80)+2*20*MAX(1,ROUND((2*1/160)*(1*1/80)*(4*1/100)*1000000,0))/1000000 = 291.7474462 3.4.16.3.                    NOT IN匹配符 NOT IN的规则与IN相似，除了选择性不同。 A:160 B:80 C:100 D:200 E:400     A:VARCHAR2 B:NUMBER C:DATE D:DATE E:NUMBER     EFFQRYCOLNUM 2           FLTCOLNUM FILTER COST_CPU CPU_A CPU_B TYPFAC CACULATED 1 a not in (:a1, :a2) 296310500 7121440 289189060 289.18906 289.18906 2 a not in (:a1, :a2, :a3) and b not in (:b1, :b2) 657035353 7121440 649913913 649.913913 649.9139131 3 a not in (:a1, :a2) and b not in (:b1) and c not in (:c1, :c2, :c3, :c4) 1635207532 7121440 1628086092 1628.086092 1628.086092 4 a not in (:a1, :a2, :a3) and b not in (:b1, :b2) and c not in (:c1, :c2) and d not in (:d1, :d2, :d3, :d4) 2383966666 7121440 2376845226 2376.845226 2376.845226 之前说过，NOT IN的选择性计算是(1-1/NDV)^NOTINNUM，其中NOTINNUM为NOT IN中的变量个数。上述第三条数据的计算为： 130+4*20+(50+50*(1-1/160)+50*(1-1/160)*(1-1/160))*1+(150+150*(1-1/80))*(1-1/160)^3+(300+300*(1-1/100))*(1-1/160)^3*(1-1/80)^2+(300+300*(1-1/200)+300*(1-1/200)*(1-1/200)+300*(1-1/200)*(1-1/200)*(1-1/200))*(1-1/160)^3*(1-1/80)^2*(1-1/100)^2+2*20*MAX(1,ROUND((1-1/160)^3*(1-1/80)^2*(1-1/100)^2*(1-1/200)^4*1000000,0))/1000000 = 2376.845226 3.4.17.           再看表的记录数 细心的读者从上面的一些数字可以看出，计算结果是被ROUNDING过的。例如IN匹配符中的例子的公式计算值为291.7474462，而由COST_CPU计算值为291.747446。通过调整TABROWS，不难发现这个ROUNDING的位置： 公式19：TYPFAC_COMP = ROUND(TYPFAC*TABROWS, 0)/TABROWS 提示：这里分母中的TABROWS最终是可以和CPU_B部分中的TABROWS抵消的，但我为了保持和前面的推导一致，暂时不把它拿掉。 3.4.18.           各种匹配符统一公式 综合各个匹配符的规则及公式，它们可以共用统一公式： 公式20：TYPFAC = ROUND((130 + MAXFLTCOLPOS*20+ COLTYPEFAC1*1+ COLTYPEFAC2*COLSEL1 + … + COLTYPEFACn*COLSEL1*…*COLSELn-1 + 20*EFFQRYCOLNUM*MAX(1,ROUND(COLSEL1*…*COLSELn*TABROWS,0))/TABROWS)*TABROWS,0)/TABROWS 其中： 数据类型 COLTYPEFAC CHAR 50 VARCHAR 50 NUMBER 150 DATE 300 各种匹配符的选择性计算如下： 匹配符 COLSEL >、<、<=、>= 1/20 LIKE 1/20 = 1/NDV <>  1-1/NDV IN NOTINNUM*1/NDV NOT IN (1-1/NDV)^NOTINNUM 当匹配符为LIKE时，COLTYPEFAC_NEW = COLTYPEFAC + 50 当匹配符为IN、NOT IN时COLTYPFAC_NEW = COLTYPEFAC*(1-1/NDV)^0+COLTYPEFAC*(1-1/NDV)^1+…+COLTYPEFAC*(1-1/NDV)^(INNUM-1) 3.4.19.           混合匹配符表达式 当各种匹配符能够使用一个统一公式时，就是开始推导混合匹配符表达式的时候了。我们假设在混合匹配符表达式中，公式20还成立，那么，这里需要做的最重要的一个工作就是确定过滤字段的排序规则。 通过大量测试数据，我发现过滤字段的处理顺序并非之前在单纯条件下那么简单： a > :v1 and b like :v2 and c IN (:c1, :c2) and d NOT IN (:d1, :d2) and e = :v5 TABROWS 10000 EFFQRYCOLNUM 0     ALLCOLDATATYPE DATE E_NDV: 400 ALLCOLNDV 200       12429415 7121440 5307975 530.7975 530.7975 TABROWS 1000000           537918968 7121440 530797528 530.797528 530.7975 a > :v1 and e = :v5             537871440 7121440 530750000 530.75 530.75 a > :v1 and b like :v2 and e = :v5             537915190 7121440 530793750 530.79375 530.79375 a > :v1 and b like :v2 and c IN (:c1, :c2) and d NOT IN (:d1, :d2) and e = :v5 TABROWS 1000000 EFFQRYCOLNUM 0 ALLCOLNDV 200 ALLCOLDATATYPE DATE   287396640 7121440 280275200 280.2752 280.2751869 a > :v1 and b like :v2 and c IN (:c1, :c2)           252158940 7121440 245037500 245.0375 245.0375   从这些数据中，最终得出的对排序规则这样一个结论：优化器采用一种这样的排序规则：在对两个字段A、B进行比较时，设2组计算式，将A的COLTYPFAC乘以B的COLSEL (先A再B)，将B的COLTYPFAC乘以A的COLSEL (先B再A)，对两组结果进行比较，采用值最小的计算式的顺序进行排序。这个排序规则是对公式20的一个充分补充。