Change in computation of IXSEL between 8.1.6 and 8.1.7

Hello List,
In 8.1.6.3.0, 64 bit (HP-UX) gives the predicate

 

ca.column1 = '40' and ca.column2 like '5.13341%'

 

an IXSEL of 5.8878e-07  (corresponding exactly to density of the column2). See 
extracts of 10053 event trace:

 

Table stats    Table: TABLE1   Alias: CA

  TOTAL ::  CDN: 1698425  NBLKS:  32089  TABLE_SCAN_CST: 7687  AVG_ROW_LEN:  281

-- Index stats

  INDEX#: 45231  COL#: 3 11 

    TOTAL ::  LVLS: 2   #LB: 3365  #DK: 1573698  LB/K: 1  DB/K: 1  CLUF: 669635

 

***************************************

SINGLE TABLE ACCESS PATH

Column:    COLUMN1  Col#: 11     Table: TABLE1   Alias: CA

    NDV: 5         NULLS: 0         DENS: 2.0000e-01

Column:   COLUMN2  Col#: 3      Table: TABLE1   Alias: CA

    NDV: 1698425   NULLS: 0         DENS: 5.8878e-07

  TABLE: TABLE1   ORIG CDN: 1698425  CMPTD CDN: 1

  Access path: tsc  Resc:  7687  Resp:  7687

  Access path: index (scan)

      INDEX#: 45231  TABLE: TABLE1   

      CST: 4  IXSEL:  5.8878e-07  TBSEL:  1.1776e-07

  BEST_CST: 4.00  PATH: 4  Degree:  1

***************************************

 

In 8.1.7.4.0 - 64bit gives the same predicate IXSEL of only 9.0000e-03  (4 
order of magnitude difference). The data is not exactly the same, but this 
cannot explain this big difference.

 

Table stats    Table: TABLE1   Alias: CA

  TOTAL ::  CDN: 2008642  NBLKS:  37374  TABLE_SCAN_CST: 8952  AVG_ROW_LEN:  279

-- Index stats

  INDEX#: 45231  COL#: 3 11 

    TOTAL ::  LVLS: 2   #LB: 3979  #DK: 1671180  LB/K: 1  DB/K: 1  CLUF:  687040

 

***************************************

SINGLE TABLE ACCESS PATH

Column:    COLUMN1  Col#: 11     Table: TABLE1   Alias: CA

    NDV: 5         NULLS: 0         DENS: 2.0000e-01

Column:   COLUMN2  Col#: 3      Table: TABLE1   Alias: CA

    NDV: 2008642   NULLS: 0         DENS: 4.9785e-07

  TABLE: TABLE1   ORIG CDN: 2008642  CMPTD CDN: 20087

  Access path: tsc  Resc:  8952  Resp:  8952

  Access path: index (scan)

      INDEX#: 45231  TABLE: TABLE1   

      CST: 1275  IXSEL:  9.0000e-03  TBSEL:  1.8000e-03

  BEST_CST: 1275.00  PATH: 4  Degree:  1

***************************************

 

Was the algorithm of computing of IXSEL changed in 8.1.7?

I found similar bug relevant to 9.2 on metalink 2991526 resp. 3009559 
(SELECTIVITY OF LIKE PREDICATE DIFFERENT IN 9203 FROM 8174)

 

Is there a workaround to this bug/feature? Any explanations and hints 
appreciated!

 

The main problem:

The low index selectivity (in 8.1.7) doesn't disable the index as access path 
but the high value of CMPTD CDN leads in joins to preferring merge joins over 
nested loops.

 

Btw, I would expect the filter factor of the mentioned predicate to be as 
follows (based on the description in A Look under the Hood ..):

 

density of column1 (rule for predicate c1 = value)

times (rule for predicate AND predicate)

density of column2 (rule for predicate c1 LIKE value)

 

This formula describes in 8.1.6 not IXSEL but TBSEL; interestingly though IXSEL 
in 8.1.7 is complete different the ratio of TBSEL / IXSEL remain the same - 1/5 
(Is this the density of column1??) 

 

 

Thanks

Jaromir

--
http://www.freelists.org/webpage/oracle-l

Other related posts: