[中文] Search Internet Search HelloDBA
Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com

# 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