Re: Index suppression

From: <genegurevich_at_discoverfinancial.com>
Date: Fri, 4 Aug 2006 15:46:26 -0500
Message-ID: <OFBF837BE3.E3338849-ON862571C0.00715E23-862571C0.0071FD33@discoverfinancial.com>

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

Received on Fri Aug 04 2006 - 15:46:26 CDT