SQL索引的问题

现有一index:
create index DM_USER.SD_D_WRKR_IX11 on DM_USER.SD_D_WRKR (ORIG_HIRE_DT, HIRE_ACTN_CD)
  tablespace DM_BB_TBL
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );SQL1:
SELECT
  'Total',
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD,
  SD_D_CTRY_HIER_WW.CTRY_NM,
  SD_D_CTRY_HIER_WW.RGN_NM,
  SD_D_CTRY_HIER_WW.SUB_RGN_NM,
  SD_D_FIN_CST_CTR_WW.FUNC_AREA_CST_OF_SLS_FG,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_1_DN,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_2_DN,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_3_DN,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_4_DN,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_5_DN,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_6_DN,
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_DN,
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_DN || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_DN || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_DN || ' - ' ||SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD,
  SD_D_CTRY_HIER_WW.RGN_NM,
  SD_D_CTRY_HIER_WW.SUB_RGN_NM,
  SD_D_CTRY_HIER_WW.CTRY_NM,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_1_CD,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_2_CD,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_3_CD,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_4_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_DN,
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_DN,
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_DN,
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD,
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_DN,
  SD_D_CTRY_HIER_WW.RGN_CD,
  SD_D_CTRY_HIER_WW.SUB_RGN_CD,
  SD_D_CTRY_HIER_WW.CTRY_ISO_CD,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_5_CD,
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_6_CD,
  SD_D_CTRY_HIER_WW.CTRY_ISO_CD,
  SD_D_CTRY_HIER_WW.RGN_cd,
  SD_D_CTRY_HIER_WW.SUB_RGN_CD,
  SD_D_MTH_WW.RPT_DSPLY_DT,
  'Current FY',
  Sum(case when
 BB_D_JOB_REQ_WW.JOB_TYPE_CD = '5'  THEN DM_USER.BB_F_WRKR_COVRG_DTL.HEAD_CT else 0 end),
  (( Sum(case when
 BB_D_JOB_REQ_WW.JOB_TYPE_CD = '5' THEN DM_USER.BB_F_WRKR_COVRG_DTL.HEAD_CT else 0 end) )),
  (( Sum(case when
 BB_D_JOB_REQ_WW.JOB_TYPE_CD = '5' and SD_D_WRKR_WW.GNDR_CD = 'F'  THEN DM_USER.BB_F_WRKR_COVRG_DTL.HEAD_CT else 0 end) )),
  (( Sum(case when
 BB_D_JOB_REQ_WW.JOB_TYPE_CD = '5' and SD_D_WRKR_WW.GNDR_CD = 'F' and SD_D_CTRY_HIER_WW.CTRY_ISO_CD <>'USA'  THEN DM_USER.BB_F_WRKR_COVRG_DTL.HEAD_CT else 0 end) )),
  (( Sum(case when BB_D_JOB_REQ_WW.JOB_TYPE_CD = '5' and SD_D_CTRY_HIER_WW.CTRY_ISO_CD <>'USA' THEN DM_USER.BB_F_WRKR_COVRG_DTL.HEAD_CT else 0 end) )),
  SD_D_MTH_WW.RPT_DSPLY_DT,
  SD_D_PAYRL_CTRY_HIER_WW.CTRY_ISO_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.CTRY_NM,
  SD_D_PAYRL_CTRY_HIER_WW.RGN_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.RGN_NM,
  SD_D_PAYRL_CTRY_HIER_WW.SUB_RGN_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.SUB_RGN_NM
FROM
  SD_D_BUS_HIER  SD_D_BUS_HIER_WW,
  SD_D_CTRY_HIER  SD_D_CTRY_HIER_WW,
  SD_D_FIN_CST_CTR  SD_D_FIN_CST_CTR_WW,
  SD_D_PRFT_CTR  SD_D_PRFT_CTR_WW,
  SD_D_MTH  SD_D_MTH_WW,
  DM_USER.BB_F_WRKR_COVRG_DTL,
  BB_D_JOB_REQ  BB_D_JOB_REQ_WW,
  SD_D_WRKR  SD_D_WRKR_WW,
  SD_D_CTRY_HIER  SD_D_PAYRL_CTRY_HIER_WW
WHERE
  ( SD_D_WRKR_WW.BB_D_JOB_REQ_ID_FK=BB_D_JOB_REQ_WW.BB_D_JOB_REQ_ID(+)  )
  AND  ( SD_D_MTH_WW.RPT_DSPLY_FG = 'Y'  )
  AND  ( DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_MTH_ID_FK=SD_D_MTH_WW.SD_D_MTH_ID  )
  AND  ( DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_WRKR_ID_FK=SD_D_WRKR_WW.SD_D_WRKR_ID  )
  AND  ( DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_PRFT_CTR_ID_FK=SD_D_PRFT_CTR_WW.SD_D_PRFT_CTR_ID  )
  AND  ( DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_FIN_CST_CTR_ID_FK=SD_D_FIN_CST_CTR_WW.SD_D_FIN_CST_CTR_ID  )
  AND  ( SD_D_CTRY_HIER_WW.SD_D_CTRY_HIER_ID=DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_CTRY_HIER_ID_FIN_FK  )
  AND  ( SD_D_PAYRL_CTRY_HIER_WW.SD_D_CTRY_HIER_ID=DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_CTRY_HIER_ID_PYRL_FK  )
  AND  ( SD_D_BUS_HIER_WW.SD_D_BUS_HIER_ID=DM_USER.BB_F_WRKR_COVRG_DTL.SD_D_BUS_HIER_ID_FIN_FK  )
  AND  
  (
   SD_D_MTH_WW.RPT_DSPLY_DT  =  '07-FEB-2010'
   AND
   SD_D_WRKR_WW.REGULAR_TEMP_CD  =  'R'
   AND
   (
    (
     SD_D_WRKR_WW.HIRE_ACTN_CD  =  'HIR'
     AND
      SD_D_WRKR_WW.ORIG_HIRE_DT  >=  SD_D_MTH_WW.fy_bgn_dt
     AND
      SD_D_WRKR_WW.ORIG_HIRE_DT  <=  SD_D_MTH_WW.RPT_DSPLY_DT
     AND
      SD_D_WRKR_WW.ACQ_CO_CD  Is Null  
    )
    OR
    (
     SD_D_WRKR_WW.HIRE_ACTN_CD  =  'REH'
     AND
      SD_D_WRKR_WW.REHIRE_DT  >=  SD_D_MTH_WW.fy_bgn_dt
     AND
      SD_D_WRKR_WW.REHIRE_DT  <=  SD_D_MTH_WW.RPT_DSPLY_DT
    )
   )
  )
GROUP BY
  'Total', 
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD, 
  SD_D_CTRY_HIER_WW.CTRY_NM, 
  SD_D_CTRY_HIER_WW.RGN_NM, 
  SD_D_CTRY_HIER_WW.SUB_RGN_NM, 
  SD_D_FIN_CST_CTR_WW.FUNC_AREA_CST_OF_SLS_FG, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_1_DN, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_2_DN, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_3_DN, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_4_DN, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_5_DN, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_6_DN, 
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_DN, 
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_DN || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_DN || ' - ' || SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_DN || ' - ' ||SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD, 
  SD_D_CTRY_HIER_WW.RGN_NM, 
  SD_D_CTRY_HIER_WW.SUB_RGN_NM, 
  SD_D_CTRY_HIER_WW.CTRY_NM, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_1_CD, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_2_CD, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_3_CD, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_4_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_1_GRP_DN, 
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_2_UNIT_DN, 
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_3_ORG_CHART_DN, 
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_CD, 
  SD_D_BUS_HIER_WW.BUS_LVL_4_MRU_DN, 
  SD_D_CTRY_HIER_WW.RGN_CD, 
  SD_D_CTRY_HIER_WW.SUB_RGN_CD, 
  SD_D_CTRY_HIER_WW.CTRY_ISO_CD, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_5_CD, 
  SD_D_PRFT_CTR_WW.PRFT_CTR_LVL_6_CD, 
  SD_D_CTRY_HIER_WW.CTRY_ISO_CD, 
  SD_D_CTRY_HIER_WW.RGN_cd, 
  SD_D_CTRY_HIER_WW.SUB_RGN_CD, 
  SD_D_MTH_WW.RPT_DSPLY_DT, 
  'Current FY', 
  SD_D_MTH_WW.RPT_DSPLY_DT, 
  SD_D_PAYRL_CTRY_HIER_WW.CTRY_ISO_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.CTRY_NM, 
  SD_D_PAYRL_CTRY_HIER_WW.RGN_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.RGN_NM, 
  SD_D_PAYRL_CTRY_HIER_WW.SUB_RGN_CD||' - '||SD_D_PAYRL_CTRY_HIER_WW.SUB_RGN_NM
走索引SD_D_WRKR_IX11。SQL2:
把WHERE条件改成如下，其他不变
  AND  
  (
   SD_D_MTH_WW.RPT_DSPLY_DT  =  '07-FEB-2010'
   AND
   SD_D_WRKR_WW.REGULAR_TEMP_CD  =  'R'
   AND
   (
    (
     SD_D_WRKR_WW.HIRE_ACTN_CD  IN ('HIR','REH','*')
     AND
      SD_D_WRKR_WW.ORIG_HIRE_DT  >=  SD_D_MTH_WW.fy_bgn_dt
     AND
      SD_D_WRKR_WW.ORIG_HIRE_DT  <=  SD_D_MTH_WW.RPT_DSPLY_DT
     AND
      SD_D_WRKR_WW.ACQ_CO_CD  Is Null  
    )
    OR
    (
     SD_D_WRKR_WW.HIRE_ACTN_CD  IN ('HIR','REH','*')
     AND
      SD_D_WRKR_WW.REHIRE_DT  >=  SD_D_MTH_WW.fy_bgn_dt
     AND
      SD_D_WRKR_WW.REHIRE_DT  <=  SD_D_MTH_WW.RPT_DSPLY_DT
    )
   )
 不走索引，Table Access Full.SD_D_WRKR.HIRE_ACTN_CD只有三个值('HIR','REH','*')。
select count(*) from SD_D_WRKR where HIRE_ACTN_CD = '*' 15006591条记录  
                                                    'HIR' 394031条记录   
                                                    'REH' 120921条记录
哪位高手帮忙介绍下，下面为何不走索引，如何解决。3Q3Q!

解决方案 »

  1.   

    你的HIRE_ACTN_CD用了IN嘛，IN会走全表
      

  2.   

    有可能是Oracle根据的SQL查询语句做了分析，认为第二种方案走全表扫描更有效，一般来说字段有索引，而执行计划没有走index，一是表有过旧的统计信息（此题你的需求不满足，因为之前是走index的），二是你条件查询返回的记录数超过总记录数5%，这时，Oracle会认为走全表扫描更有效，因为走index回耗更多的IO，根据不同的情况Oracle选择走RBO道路（没仔细看你的sql，比较难让人静下心来给你看那么多的SQL代码，而且感觉是杂乱的，不好看，不管怎样，通过你锁说的，有可能是你第二种方案条件变了，返回记录数多，导致执行计划走全表扫描）。