Re: wrong index IX_SEL?

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Wed, 12 Mar 2008 04:06:32 -0700 (PDT)
Message-ID: <e502dec3-1479-4f8a-a8b4-445d54bd73f3@i12g2000prf.googlegroups.com>

On Mar 12, 1:29 pm, "jog..._at_gmail.com" <jog..._at_gmail.com> wrote:
> Sachin, both columns are indexed:
> Index: IDT_FCMO_TMS Col#: 3 4
>
> SINGLE TABLE ACCESS PATH
> Column (#3): CMO_CODTSG(VARCHAR2)
> AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619
> Column (#4): CMO_CODTMS(VARCHAR2)
> AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584
>
> I agree with you, it seems that in 10.2 the behavior has changed, so
> that explains the numbers.
>
> Thanks.
>
> Joaquín González
>
> On 12 mar, 09:12, Ind-dba <oraclear..._at_googlemail.com> wrote:
>
> > On Mar 12, 12:25 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
>
> > > On Mar 11, 11:54 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> > > wrote:
>
> > > > 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
> > > >> READ the original post and you'll FIND much of that information.
>
> > > "Much" but not all.
>
> > > >> You miss much in your zeal to be 'right'.
>
> > > Too early to comment :)
>
> > > >> David Fitzjarrell
>
> > > Your SQL is :
> > > select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
> > > CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL'
>
> > > Are both these columns indexed?
> > > CMO_CODTSG AND CMO_CODTMS
>
> > > Or only the CMO_CODTMS indexed?
>
> > > Sachin
>
> > It seems the book was written when 10.2 was just released and this
> > change was not tested.
>
> > What you were calculating by multiplying densities holds good for 9i
> > versions (not sure of 10g rel 1).
> > But in 10g rel 2, there have been changes in optimizer. And this is
> > probably one of them.
>
> > Please check out :http://jonathanlewis.wordpress.com/2007/02/15/index-not-used-10g/
>
> > This is quite similar to our topic of discussion here.
>
> > Thanks,
> > Sachin

Interesting thing is - if you collect the stats using histograms and have separate index for each of the column. You may notice (if the data is not highly skewed - i have not tested with skewed data), densities getting multiplied and both the indexes being used in Bitmap index pattern. here is the example:

 select count(*) from test where a=5 and b=20

---

Plan Table

---

---

+-----------------------------------+
| Id  | Operation                         | Name    | Rows  | Bytes |
Cost  | Time      |
----------------------------------------------------
+-----------------------------------+
| 0   | SELECT STATEMENT                  |         |       |
|     2 |           |
| 1   |  SORT AGGREGATE                   |         |     1 |     6
|       |           |
| 2   |   BITMAP CONVERSION COUNT         |         |    32 |   192
|     2 |  00:00:01 |
| 3   |    BITMAP AND                     |         |       |
|       |           |
| 4   |     BITMAP CONVERSION FROM ROWIDS |         |       |
|       |           |
| 5   |      INDEX RANGE SCAN             | TEST_A  |       |
|     1 |  00:00:01 |
| 6   |     BITMAP CONVERSION FROM ROWIDS |         |       |
|       |           |
| 7   |      INDEX RANGE SCAN             | TEST_B  |       |
|     1 |  00:00:01 |
----------------------------------------------------
+-----------------------------------+

***************************************

BASE STATISTICAL INFORMATION

---

Table Stats::
  Table: TEST Alias: TEST
    #Rows: 2048 #Blks: 5 AvgRowLen: 6.00 Index Stats::
  Index: TEST_A Col#: 1
    LVLS: 1 #LB: 4 #DK: 8 LB/K: 1.00 DB/K: 4.00 CLUF: 32.00   Index: TEST_B Col#: 2
    LVLS: 1 #LB: 4 #DK: 8 LB/K: 1.00 DB/K: 4.00 CLUF: 32.00

---

SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 3.00 NDV: 8 Nulls: 0 Density: 0.125 Min: 1 Max: 8   Column (#2): B(NUMBER)
    AvgLen: 3.00 NDV: 8 Nulls: 0 Density: 0.125 Min: 10 Max: 80   Table: TEST Alias: TEST
    Card: Original: 2048 Rounded: 32 Computed: 32.00 Non Adjusted: 32.00
  Access Path: TableScan
    Cost: 3.08 Resp: 3.08 Degree: 0

      Cost_io: 3.00  Cost_cpu: 498967
      Resp_io: 3.00  Resp_cpu: 498967
  Access Path: index (AllEqRange)

    Index: TEST_A
    resc_io: 5.00 resc_cpu: 143977
    ix_sel: 0.125 ix_sel_with_filters: 0.125     Cost: 5.02 Resp: 5.02 Degree: 1
  Access Path: index (AllEqRange)
    Index: TEST_B
    resc_io: 5.00 resc_cpu: 143977
    ix_sel: 0.125 ix_sel_with_filters: 0.125     Cost: 5.02 Resp: 5.02 Degree: 1

• trying bitmap/domain indexes ****** Access Path: index (AllEqRange) Index: TEST_A resc_io: 1.00 resc_cpu: 59171 ix_sel: 0.125 ix_sel_with_filters: 0.125 Cost: 1.01 Resp: 1.01 Degree: 0 Access Path: index (AllEqRange) Index: TEST_B resc_io: 1.00 resc_cpu: 59171 ix_sel: 0.125 ix_sel_with_filters: 0.125 Cost: 1.01 Resp: 1.01 Degree: 0 Access path: Bitmap index - accepted Cost: 2.03 Cost_io: 2.00 Cost_cpu: 187463 Sel: 0.015625 --> This comes from multiplying 0.125 and 0.125 i.e. both the densities. Believed to be index-only

BTW - I had some issues reading the trace submitted by you and that is why confusion started. "col 3 4" were read as "col 34".

Thanks,
Sachin Received on Wed Mar 12 2008 - 06:06:32 CDT