RE: performance question

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Mon, 10 Sep 2007 17:19:05 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC48242BC7@LIMENS.sivsa.int>

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
--

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 10 2007 - 10:19:05 CDT