Full Table Scan cost formula cracking(4)
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2009-08-01 15:03:34
Share to
3.4.11. Max filter column position (with mixed filter column data type)
When I using a mixed filter column data type in the test, I found the effect of MAXFILPOS in the formula is not linear, but changes is still regular:
date
NUMBER
MAXFILPOS
5
EFFCOLNUMS
5
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
387121440
7121440
380000000
380
2
402121440
7121440
395000000
395
3
402871440
7121440
395750000
395.75
4
402908940
7121440
395787500
395.7875
5
402910815
7121440
395789375
395.78938
date
NUMBER
MAXFILPOS
4
EFFCOLNUMS
5
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
368121440
7121440
361000000
361
19
2
382171440
7121440
375050000
375.05
19.95
3
382873940
7121440
375752500
375.7525
19.9975
4
382909060
7121440
375787620
375.78762
19.99988
date
NUMBER
MAXFILPOS
3
EFFCOLNUMS
5
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
349121440
7121440
342000000
342
19
2
362221440
7121440
355100000
355.1
19.95
3
362876440
7121440
355755000
355.755
19.9975
Observe the data, I finally get the rule:
F(MAXFILPOS)–F(MAXFILPOS-1) = 20–20^(1-FLTCOLNUM)
Put this equation into formula 12, we get the new formula:
Formula 13: TYPFAC = 130 + EFFCOLNUMS + 5*19 - (5-MAXFLTCOLPOS)*(20–20^(1-FLTCOLNUM)) + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
= 225 + EFFCOLNUMS - (5-MAXFLTCOLPOS)*(20–20^(1-FLTCOLNUM)) + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
= 225 + EFFCOLNUMS -100 + MAXFLTCOLPOS*20 + (5-MAXFLTCOLPOS)*20^(1-FLTCOLNUM) + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
= 125 + EFFCOLNUMS + MAXFLTCOLPOS*20 + (5-MAXFLTCOLPOS)*20^(1-FLTCOLNUM) + COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
3.4.12. Effective column number (with mixed filter column data type)
Besides MAXFLTCOLPOS, I also found other factor violated the formula we ever got in the query with mixed filter column data type, which is EFFCOLNUM:
date
NUMBER
MAXFILPOS
3
EFFCOLNUMS
5
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
349121440
7121440
342000000
342
2
362221440
7121440
355100000
355.1
3
362876440
7121440
355755000
355.755
date
NUMBER
MAXFILPOS
3
EFFCOLNUMS
4
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
348121440
7121440
341000000
341
1
2
362171440
7121440
355050000
355.05
0.05
3
362873940
7121440
355752500
355.7525
0.0025
date
NUMBER
MAXFILPOS
3
EFFCOLNUMS
3
FLTCOLNUM
resc_cpu
CPU_B
CPU_A
TYPFAC
1
347121440
7121440
340000000
340
1
2
362121440
7121440
355000000
355
0.05
3
362871440
7121440
355750000
355.75
0.0025
Fortunately, the rule is not hard to be found:
F(EFFCOLNUMS)–F(EFFCOLNUMS-1) = 20^(1-FLTCOLNUM)
Modify formula 13 base on this rule:
Formula 14: TYPFAC = 130+(20*EFFCOLNUM-100)*(20^(-FILTCLNUM))+MAXFLTCOLPOS*20+(5-MAXFLTCOLPOS)*20^(1-FLTCOLNUM)+ COLTYPEFAC1*(1/20)^0 + COLTYPEFAC2*(1/20)^1 + … + COLTYPEFACn*(1/20)^(FLTCOLNUM-1)
&nb