Re: wrong index IX_SEL?

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 Received on Mon Mar 10 2008 - 12:48:15 CDT