wrong index IX_SEL?

From: <jogoca_at_gmail.com>
Date: Mon, 10 Mar 2008 09:13:29 -0700 (PDT)
Message-ID: <a8dc8e8c-9af0-4f8c-a5e8-5216fbba460c@u72g2000hsf.googlegroups.com>

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 Received on Mon Mar 10 2008 - 11:13:29 CDT