Hemank, Thanks for the suggestion about the fix. At this moment, I am more interested in how the selectivity is calculated. Inspired by some google results, I think I probably find the answer. Here is the stats from dba_indexes: From DBA_INDEXES ~~~~~~~~~~~~~~~~~ .INDEX_NAME : SMC_MAP_CURRSTAT_NUK .INDEX_TYPE : NORMAL .LOGGING : NO .BLEVEL : 3 .LEAF_BLOCKS : 752377 .DISTINCT_KEYS : 558 ..LAST_ANALYZED : 10/19/2010 23:41:42 .DEGREE : 1 .... It seems the selectivity comes from : 1/DISTINCT_KEYS = 1/558=0.001792, this matches perfectly with 10053 trc ix_sel : 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 Now the question is why or what controls Oracle CBO to choose index stats info to calculate the composite index selectivity? This db was upgraded from 9i not long time ago, I did not remember we had same issue in 9i. Unfortunately it is not easy for me to find a 9i db and exp/imp this 50GB table to test. Could it be some hidden paramter change that controls the behavior? ________________________________ From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> To: denis.sun@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Sent: Wed, October 20, 2010 10:30:53 AM Subject: 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