I am sorry, here is the sql statement;
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
Alvaro Jose Fernandez wrote:
> Joan,
>
> it seems the excerpt you posted is incomplete (I cannot understand that the last op is the hash join with id=13 on both plans)
>
> please, ¿would you mind to post a tkprof-processed plan of both (without running the "explain= " param of tkprof), just to
> compare why the diff in rowcounts?
>
> QA
> .
> .
>
> STAT #2 id=13 cnt=8538 pid=2 pos=2 obj=0 op='HASH JOIN (cr=217257
> pr=23078 pw=4941 time=41906027 us)'
>
> TRN
> .
> .
> .
> .
> STAT #1 id=13 cnt=0 pid=2 pos=2 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0
> time=0 us)'
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 10 2007 - 09:38:37 CDT
