Re: wrong index IX_SEL?

From: <fitzjarrell_at_cox.net>
Date: Tue, 11 Mar 2008 11:54:49 -0700 (PDT)
Message-ID: <868eb921-a2e9-452c-bcd4-37a684815e6e@s13g2000prd.googlegroups.com>


On Mar 11, 1:27†pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> On Mar 11, 10:32 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> wrote:
>
>
>
>
>
> > On Mar 11, 2:19 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
>
> > > 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.
>
> > Because this is a single-table query, not a table join.
>
> > > selectivity is fine. It should and is :
> > > 1/NDV = 1/471 (471 comes from DK of IDT_FCMO_TMS stats).
>
> > Only for a join query with equality on the entire index. †This query
> > doesn't satisfy that criteria. †Thus the single-table selectivity is
> > the product of the individual column selectivities (being that the
> > conjunction is AND, not OR):
>
> > select /* PRUEBA */ count(*)
> > FROM TDTC_CAPACIDAD_MODALIDAD
> > WHERE CMO_CODTSG = 'HT'
> > AND CMO_CODTMS = 'DBL';
>
> > > = 0.0021231422505307855626326963906582 (which is close to what
> > > optimizer has given).
>
> > Which may be why the optimizer obtained an apparently incorrect
> > result.
>
> > > - sachin- Hide quoted text -
>
> > > - Show quoted text -
>
> > David Fitzjarrell
>
> Please share some information of indexes (i mean on which column these
> indexes are created.)
>
> Are there any views in picture.
> What is the execution plan of SQL?
>
> Sachin- Hide quoted text -
>
> - Show quoted text -

READ the original post and you'll FIND much of that information.

You miss much in your zeal to be 'right'.

David Fitzjarrell Received on Tue Mar 11 2008 - 13:54:49 CDT

Original text of this message