Cost, selectivity and cardinality - all changes in 10g rel2 10053 trace file

Few days back o one of the google groups, there was issue posted by a gentleman about "index selectivity". Per him the "index selectivity" should be different than what is being shown by the optimizer. He was inquiring whether the procedure he followed to calculate "index selectivity" is correct or not.

Here is what he had to say:

I don't know how the optimizer has calculated ix_sel of the index
IDT_FCMO_TMS.
Oracle version: 10.2.0.3.0
Any idea?

SQL Statement:
select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL

10053 Trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TDTC_CAPACIDAD_MODALIDAD Alias: TDTC_CAPACIDAD_MODALIDAD
#Rows: 82240 #Blks: 1132 AvgRowLen: 82.00
Index Stats::
Index: IDT_FCMO_CHA Col#: 5
LVLS: 1 #LB: 170 #DK: 939 LB/K: 1.00 DB/K: 14.00 CLUF:13425.00
Index: IDT_FCMO_TMS Col#: 34 <----- the index
LVLS: 1 #LB: 214 #DK: 471 LB/K: 1.00 DB/K: 26.00 CLUF:12648.00 <----- the index
Index: IDT_FCMO_TSU Col#: 31
LVLS: 0 #LB: 1 #DK: 58 LB/K: 1.00 DB/K: 6.00 CLUF: 370.00
Index: RDT_PCMO Col#: 1 2
LVLS: 1 #LB: 206 #DK: 82061 LB/K: 1.00 DB/K: 1.00 CLUF:28928.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#3): CMO_CODTSG(VARCHAR2)
AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619 <---------- first column of the index
Column (#4): CMO_CODTMS(VARCHAR2)
AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584 <---------- second column of the index
Table: TDTC_CAPACIDAD_MODALIDAD Alias:
TDTC_CAPACIDAD_MODALIDAD
Card: Original: 82240 Rounded: 10 Computed: 10.12 Non Adjusted:
10.12
Access Path: TableScan
Cost: 184.60 Resp: 184.60 Degree: 0
Cost_io: 181.00 Cost_cpu: 29454495
Resp_io: 181.00 Resp_cpu: 29454495
Access Path: index (index (FFS))
Index: IDT_FCMO_TMS
resc_io: 36.00 resc_cpu: 15484960
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 37.89 Resp: 37.89 Degree: 1
Cost_io: 36.00 Cost_cpu: 15484960
Resp_io: 36.00 Resp_cpu: 15484960
Access Path: index (AllEqRange)
Index: IDT_FCMO_TMS
resc_io: 1.00 resc_cpu: 42971
ix_sel: 0.0021231 ix_sel_with_filters: 0.0021231 <-------------------- wrong ix_sel?
Cost: 1.01 Resp: 1.01 Degree: 1
Best:: AccessPath: IndexRange Index: IDT_FCMO_TMS
Cost: 1.01 Degree: 1 Resp: 1.01 Card: 10.12 Bytes: 0
***************************************

IX_SEL should be 0.047619 * 0.002584 (which is far from what optimizer showed i.e 0.0021231)

According to Jonathan lewis book "Cost-Based Oracle Fundamentals", it is well explain in Chapter-4 P 66,67 that in case of select using multiple column index, index selectivity is calculated using the multiplication of individual selectivities of columns.
And that is precisely what the person seeking support showed.

While testing this result on 10.2.0.2, I found the index selectivity never gets multiplied in case of multicolumn index. In fact optimizer calculates index selectivity based on "distinct keys" of index.In this case (per the trace shown above) it is 471.
So the index selectivity comes out to be 1/(DK of index)=1/471=0.0021231, which is what is shown by optimizer.

I think, by the time Jonathan's book was published, not all the changes were tested. In fact I visited his blog and some couple of finding similar to this posted there.