| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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#: 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#: 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-lReceived on Sun Sep 19 2004 - 15:32:15 CDT
 |
 |