RE: performance question
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: "Joan Hsieh" <joan.hsieh@xxxxxxxxx>
- Date: Mon, 10 Sep 2007 17:19:05 +0200
Joan,
One thing more. PS_TFTH_JOB (TJOB) have no filter predicates, is that's
right?
I ask because the merge join cartesian's
alvaro
--
select
JOB.EMPLID,
JOB.EFFDT,
JOB.EMPL_RCD,
Job.STD_HOURS,
tJOB.TFTH_RPT_FTE,
TJOB.TFTH_EMPL_SEC_CLAS,
TJOB.TFTH_ACCRUAL_GRP,
PD.NAME,
EMP.SERVICE_DT
from PS_JOB JOB, PS_PERSONAL_DATA PD, PS_TFTH_JOB TJOB, PS_EMPLOYMENT
EMP
where JOB.EMPLID = PD.EMPLID
AND JOB.EMPL_STATUS in ('A','L')
AND JOB.EMPLID = TJOB.EMPLID
AND JOB.EMPL_RCD = TJOB.EMPL_RCD
AND JOB.EFFDT = TJOB.EFFDT
AND JOB.EFFSEQ = TJOB.EFFSEQ
and JOB.EMPLID = EMP.EMPLID
and job.empl_rcd = emp.empl_rcd
AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1
WHERE JOB.EMPLID = JOB1.EMPLID
AND JOB1.EFFDT <= '15-AUG-2007'
AND JOB.EMPL_RCD = JOB1.EMPL_RCD)
AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2
WHERE JOB.EMPLID = JOB2.EMPLID
AND JOB.EMPL_RCD = JOB2.EMPL_RCD
AND JOB.EFFDT = JOB2.EFFDT)
or ((JOB.ACTION_DT between '15-AUG-2007' and '31-AUG-2007')
and (JOB.EFFDT < '15-AUG-2007'))
ORDER BY TJOB.TFTH_ACCRUAL_GRP, JOB.EMPLID
--
//www.freelists.org/webpage/oracle-l
Other related posts:
