Re: wrong index IX_SEL?

On Mar 11, 12:21 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 10, 1:25 pm, "jog..._at_gmail.com" <jog..._at_gmail.com> wrote:
>
>
>
> > I forgot to include it.
>
> > ******************************************
> > Current SQL statement for this session:
> > select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
> > CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL'
>
> > Joaquín González
>
> > On 10 mar, 18:48, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Mar 10, 11:13 am, "jog..._at_gmail.com" <jog..._at_gmail.com> wrote:
>
> > > > Hello all,
>
> > > > 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?
>
> > > > ***************************************
> > > > 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#: 3
> > > > 4 <-----
> > > > 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 = 0.000005859398712024
> > > > Am i wrong?
>
> > > > Thank you very much.
>
> > > > Joaquín González
>
> > > What was the query which generated this 10053 trace?
>
> > > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> ix_sel is computed, for each column, as follows:
>
> 1/NDV == column selectivity
>
> Calculating the selectivities:
>
> CMO_CODTSG selectivity == 1/21 == 0.047619, which agrees with the CBO
> computation
> CMO_CODTMS selectivity == 1/387 == 0.002584, which again agrees with
> the CBO
>
> Thus the index selectivity for this query would be:
>
> CMO_CODTSG selectivity * CMO_CODTMS selectivity
>
> 0.047619 * 0.002584
>
> 1.230466*10^-4 or 0.0001230466 (depending upon your notation)
>
> which is less than the CBO calculated, but much more than your
> calculated result.
>
> I don't know how you arrived at your value.
>
> David Fitzjarrell

Not sure why are you multiplying.

selectivity is fine. It should and is :
1/NDV = 1/471 (471 comes from DK of IDT_FCMO_TMS stats). = 0.0021231422505307855626326963906582 (which is close to what optimizer has given).

• sachin