Re: Index suppression

[genegurevich@xxxxxxxxxxxxxxxxxxxxx]

Jonathan and Somckit:

The SQL is fairly long so this is going to be a long post. If you have any
thoughts on this issue, I would appreciate it.

I can't paste the explain plans as they get formatted so badly they are not
readable..

Here is the  SQL. I have highlighted my change in blue (down at the bottom
of the SQL)

SELECT tran.FEE_PRD_SLS_KEY as tran_key,
 dy.full_dt as shft_dt,
 tran.empl_key,
 emp.racf_id,
 emp.lst_nm,
 emp.prefr_nm,
 UPPER(emp.lst_nm || ', ' || emp.prefr_nm) as full_nm,
 tran.acct_nbr,
 mpt.PRDCT_TYP_DSC,
 tran.chan_hrchy_key,
 ref.chan_ref_key,
 tran.FEE_PRD_SLS_CNT,
 tran.FEE_PRD_SLS_KEY,
 tran.FEE_PRD_SLS_SEQ_NBR,
 tran.FEE_PRD_SLS_ADJ_IND,
 tran.OPERS_CNTR_CDE,
 tran.SHFT_DY_TM_PERD_KEY,
 tran.ACCT_KEY,
 tran.FEE_PRD_SLS_UP_MTH_TM_PERD_KEY,
 tran.FEE_PRD_SLS_UP_DY_TM_PERD_KEY,
 tran.CHAN_HRCHY_KEY,
 tran.MBR_PRDCT_TYP_KEY
FROM
 TELSREP.FEE_PRD_SLS tran,
 TELSREP.chan_hrchy ch,
 TELSREP.chan_ref ref,
 TELSREP.mbr_prdct_typ mpt,
 TELSREP.dy_tm_perd strt,
 TELSREP.dy_tm_perd end,
 TELSREP.dy_tm_perd dy,
 TELSREP.call_cntr_empl_mthly_hist hist,
 TELSREP.call_cntr_mgmt_hrchy hrchy,
 TELSREP.call_cntr_empl emp
WHERE
tran.EMPL_KEY  = 1877
 AND strt.full_dt = to_date('2006-07-27','yyyy-mm-dd')
 AND end.full_dt = to_date('2006-07-27','yyyy-mm-dd')
 AND tran.shft_dy_tm_perd_key between strt.dy_tm_perd_key and
end.dy_tm_perd_key
 AND dy.dy_tm_perd_key = tran.shft_dy_tm_perd_key
 AND hist.shft_mth_tm_perd_key = tran.shft_mth_tm_perd_key
 AND hist.empl_key = tran.empl_key
 AND hrchy.call_cntr_mgmt_hrchy_key = hist.call_cntr_mgmt_hrchy_key
 AND emp.empl_key+0 = tran.empl_key
 AND tran.CHAN_HRCHY_KEY = ch.chan_hrchy_key
 AND ch.chan_ref_key = ref.chan_ref_key
 AND mpt.mbr_prdct_typ_key = tran.mbr_prdct_typ_key
ORDER BY
 shft_dt, emp.lst_nm, emp.prefr_nm, chan_ref_key, mpt.PRDCT_TYP_DSC



thank you

Gene Gurevich



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

• References:
  • Re: Index suppression
    • From: Jonathan Lewis

Other related posts:

Index suppression

Re: Index suppression

RE: Index suppression

RE: Index suppression

Re: Index suppression

RE: Index suppression