Full Table Scan cost formula cracking(5)

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2009-08-01 15:03:33

Share to 

3.4.15.           Operator (LIKE) With the experience on previous formula derivation, the process is smooth in the LIKE derivation. I directly deduce the formula with mixed data types and mixed NDV (In fact, conclusion in the article 查询计划中Cardinality的计算 told us the selectivity of LIKE is same as the one of >, both is 1/20. It can be proved by following test): 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 From the test, the final rule is that the selectivity (1/NDV) is a fixed number, 1/20; however, it will increase the COLTYPFAC by 50. For example, the third one in above data set, the calculation is, 130+3*20+100*1+200*(1/20)+350*(1/20)^2 = 300.875 Then take the effective query columns into account, 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 Combine with the rule above, all of the results matched the results calculated by formula 18. 3.4.16.           Other operators (<>, IN and NOT IN) Base on the conclusion in 查询计划中Cardinality的计算, the selectivity of the other operators with bind variable is, <>: 1/NDV IN: (variable number)*(1/NDV) NOT IN: (1-1/NDV)^(variable number) Following the way I worked on < & =, it’s not hard to find the formulas. 3.4.16.1.             Operator (<>) Here is the test data for <>: 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