Re: composite index selectivity question

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: denis.sun@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 20 Oct 2010 22:30:53 +0800


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




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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2010