Re: composite index selectivity question

I bet that if you changed
SMC_MAP_CURRSTAT_NUK : --> (MAP_ID, CURR_STATUS_ID, MAP_VERSION) to
SMC_MAP_CURRSTAT_NUK :--> ( MAP_VERSION,CURR_STATUS_ID, MAP_ID) it would be a *better* index.

For the first query, Oracle isn't exactly doing the same sort of operation on the two Indexes.
The AllEqRange on SMC_MAP_CURRSTAT_NUK has a much higher I/O cost than then RangeScan on SMC_IDX4 and that is why Oracle chooses the latter index.

The expected cardinality is derived from *column* statistics.

Hemant K Chitale

At 09:22 PM Wednesday, Denis wrote:
>Hi, Listers,
>
>I had a problem and appreicate you help me to understand:
>
>Problem : suboptimal execution plan using index SMC_IDX4 selected
>by Oracle CBO
>good index: SMC_MAP_CURRSTAT_NUK(MAP_ID, CURR_STATUS_ID, MAP_VERSION)
>bad index : SMC_IDX4(MAP_ID, PREV_STATUS_ID, CURR_STATUS_ID )
>
>Question (refer to 10053 below) : how ix_sel = 0.0017921 is
>calculated for the index (SMC_MAP_CURRSTAT_NUK) access path ?
> My understanding is
> selectivity(MAP_ID,MAP_VERSION,CURR_STATUS_ID)
> = selectivity(MAP_ID) * selectivity(MAP_VERSION) *
> selectivity(CURR_STATUS_ID)
> = 0.012821 * 1 * 0.023256
> = 2.9817e-04
>Oracle version: 10.2.0.4
>SQL-1:
> SELECT pieid
> FROM SMC
> WHERE MAP_ID = 91 AND
> MAP_VERSION = 1 AND
> CURR_STATUS_ID = 10;
>
>===> from 10053 trace
>
>Table Stats::
> Table: SMC Alias: SMC
> #Rows: 299321835 #Blks: 9470379 AvgRowLen: 186.00
>-----------------------------------------
> BEGIN Single Table Cardinality Estimation
> -----------------------------------------
> Column (#8): MAP_ID(NUMBER)
> AvgLen: 4.00 NDV: 78 Nulls: 0 Density: 0.012821 Min: 1 Max: 144
> Column (#9): MAP_VERSION(NUMBER)
> AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
> Column (#7): CURR_STATUS_ID(NUMBER)
> AvgLen: 3.00 NDV: 43 Nulls: 0 Density: 0.023256 Min: 0 Max: 97
> Table: SMC Alias: SMC
> Card: Original: 299321835 Rounded: 89243 Computed:
> 89243.24 Non Adjusted: 89243.24
>
>Index: SMC_MAP_CURRSTAT_NUK Col#: 8 7 9
> LVLS: 3 #LB: 752377 #DK: 558 LB/K: 1348.00 DB/K:
> 204576.00 CLUF: 114153723.00
>Index: SMC_IDX4 Col#: 8 6 7
> LVLS: 3 #LB: 756804 #DK: 2060 LB/K: 367.00 DB/K:
> 60536.00 CLUF: 124705545.00
>
>Access Path: index (AllEqRange)
> Index: SMC_MAP_CURRSTAT_NUK
> resc_io: 205929.00 resc_cpu: 1743632975
> ix_sel: 0.0017921 ix_sel_with_filters: 0.0017921
> Cost: 206118.49 Resp: 206118.49 Degree: 1
>
>Access Path: index (RangeScan)
> Index: SMC_IDX4
> resc_io: 46888.00 resc_cpu: 1159030959
> ix_sel: 0.012821 ix_sel_with_filters: 2.9815e-04
> Cost: 47034.81 Resp: 47034.81 Degree: 1
>
>
>SQL-2:
>SELECT pieid
>FROM SMC
>WHERE MAP_ID = 91 AND
>CURR_STATUS_ID = 10;
>In this case, CBO is able to choose better index access path and
>selectivity calculation is understandable.
>Access Path: index (RangeScan)
> Index: SMC_MAP_CURRSTAT_NUK
> resc_io: 34264.00 resc_cpu: 288336758
> ix_sel: 2.9815e-04 ix_sel_with_filters: 2.9815e-04
> Cost: 34295.33 Resp: 34295.33 Degree: 1
>
>Access Path: index (RangeScan)
> Index: SMC_IDX4
> resc_io: 46888.00 resc_cpu: 1152783475
> ix_sel: 0.012821 ix_sel_with_filters: 2.9815e-04
> Cost: 47034.13 Resp: 47034.13 Degree: 1
>Thanks,
>
>Yu (Denis) Sun
>Oracle DBA

Hemant K Chitale

http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l