Change in computation of IXSEL between 8.1.6 and 8.1.7
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 19 Sep 2004 22:33:56 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
