Re: wrong index IX_SEL?

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 Received on Tue Mar 11 2008 - 13:27:02 CDT