Re: wrong index IX_SEL?

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 Received on Mon Mar 10 2008 - 14:21:23 CDT