Message-ID: <46E556ED.8060506@tufts.edu>
Date: Mon, 10 Sep 2007 10:38:37 -0400
From: Joan Hsieh <joan.hsieh@tufts.edu>
Organization: Tufts University
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007
MIME-Version: 1.0
To: Alvaro Jose Fernandez <alvaro.fernandez@sivsa.com>
CC: oracle_l <ORACLE-L@freelists.org>
Subject: Re: performance question
References: <607D6181D9919041BE792D70EF2AEC48242BB9@LIMENS.sivsa.int>
In-Reply-To: <607D6181D9919041BE792D70EF2AEC48242BB9@LIMENS.sivsa.int>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: joan.hsieh@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