Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: performance question

Re: performance question

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Mon, 10 Sep 2007 10:38:37 -0400
Message-ID: <46E556ED.8060506@tufts.edu>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US