wrong index IX_SEL?
jog...@gmail.com
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
fitzj...@cox.net
What was the query which generated this 10053 trace?
David Fitzjarrell
jog...@gmail.com
******************************************
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
fitzj...@cox.net
>
> - 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
Ind-dba
Not sure why are you multiplying.
selectivity is fine. It should and is :
1/NDV = 1/471 (471 comes from DK of IDT_FCMO_TMS stats).
= 0.0021231422505307855626326963906582 (which is close to what
optimizer has given).
- sachin
jog...@gmail.com
first column in index selectivity * second column in index selectivity
-> 0.047619 * 0.002584 = 0.0001230466.
the cbo says 1/NDV -> 1/471 = 0,002123
Jonathan Lewis, in his book (pages 66-67) says it should be first
column in index selectivity * second column in index selectivity,
doesn't he?
He says too (page 314-317) that it should be 1/NDV in the special case
of a join with equality on the entire index. This is not the case,
there is not a join.
So what is the correct answer?
Thanks for your time
Joaquin Gonzalez
fitzj...@cox.net
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
Ind-dba
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
Ind-dba
Please share some information of indexes (i mean on which column these
fitzj...@cox.net
>
> - 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
Ind-dba
"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
Ind-dba
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
jog...@gmail.com
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
jog...@gmail.com
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
Ind-dba
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