composite index selectivity question
From: Denis <denis.sun_at_yahoo.com>
Date: Wed, 20 Oct 2010 06:22:43 -0700 (PDT)
Message-ID: <797634.6859.qm_at_web57208.mail.re3.yahoo.com>
Date: Wed, 20 Oct 2010 06:22:43 -0700 (PDT)
Message-ID: <797634.6859.qm_at_web57208.mail.re3.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 20 2010 - 08:22:43 CDT