Re: Efficency

Steven R Fuller wrote:
>
> Can Anyone give me some feedback as to how I can make this run a little
> faster
>
> SELECT
> A.Loc_Date
> ,C.Location_Name
> ,B.Team_Name
> ,CONCAT(B.Lname,CONCAT(', ',B.Fname))
> ,A.Excode
> ,TO_CHAR(A.Beg_Time,'HH24:MI')
> ,DECODE(SIGN(A.End_Time -
> A.Beg_Time),-1,'23:59',TO_CHAR(A.End_Time,'HH24:MI'))
> ,E.Hlfhr
> ,Count(DISTINCT A.Excode)
> ,Decode(Sum(C.Calls_Handled),0,'<4'
>
> ,decode(Sign((Sum(C.Calls_Handled_In_Service)/Sum(C.Calls_Handled))-0.39),-1,'<4'
>
> ,decode(Sign((Sum(C.Calls_Handled_In_Service)/Sum(C.Calls_Handled))-0.59),-1,'<6'
>
> ,decode(Sign((Sum(C.Calls_Handled_In_Service)/Sum(C.Calls_Handled))-0.79),-1,'<8'
>
> ,decode(Sign((Sum(C.Calls_Handled_In_Service)/Sum(C.Calls_Handled))-1.0),-1,'<10',0)))))
> SLDECODE
>
> FROM Traffic.Segment_Half_Hour C
> ,Traffic.agent_performance B
> ,Traffic.TCS_Exception_Count A
>
> ,Traffic.Halfhr E
> ,Traffic.REL_TCS_Exception_Codes F
>
> WHERE A.Node = B.Node
> AND A.Loc_Date = B.Date_Stamp
> AND A.Epid = RTRIM(B.Unum)
> AND A.Node = C.Node
> AND A.Loc_Date = C.Date_Stamp
> AND A.Excode = F.Exception_Code
>
> AND E.Hlfhr >= ((TO_CHAR(A.Beg_Time,'HH24')*2) +
> DECODE(SIGN(TO_CHAR(A.Beg_Time,'MI')-29),-1,0,0,1,1,1)) + 1
> AND E.Hlfhr < DECODE(SIGN(A.End_Time -
> A.Beg_Time),-1,48,((TO_CHAR(A.End_Time,'HH24')*2) +
> DECODE(SIGN(TO_CHAR(A.End_Time,'MI')-29),-1,0,0,1,1,1)) + 1)
> AND E.Hlfhr = C.HalfHour_Sort
>
> AND A.Loc_Date BETWEEN TO_DATE('9/13/99','mm/dd/yy') AND
> TO_DATE('9/13/99','mm/dd/yy')
> AND NOT F.Exception_Group IN ('OVTM','PAID','UNPD')
> AND B.Team_Name LIKE 'NIS%'
>
> GROUP BY A.Loc_Date
> ,C.Location_Name
> ,B.Team_Name
> ,CONCAT(B.Lname,CONCAT(', ',B.Fname))
> ,A.Excode
> ,TO_CHAR(A.Beg_Time,'HH24:MI')
> ,DECODE(SIGN(A.End_Time -
> A.Beg_Time),-1,'23:59',TO_CHAR(A.End_Time,'HH24:MI'))
> ,E.Hlfhr
>
> ---------------------------------------------------------------
>
> Name: srfuller.vcf
> Part 1.2 Type: text/x-vcard
> Encoding: 7bit
> Description: Card for Steven R Fuller

Run it through EXPLAIN plan and post the results - it will tell you/us what indexes are being used, whats being scanned fully etc etc

cf: Server Tuning manual for how to use EXPLAIN

HTH

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."