Re: Index suppression
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: jonathan@xxxxxxxxxxxxxxxxxx, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Aug 2006 15:46:26 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
