All Forums Database
ciw916 8 posts Joined 11/12
21 Nov 2012
Need help to avoid spool space error

I have a query that I am trying to run against teradata.  I received an email stating that my query was using too much of the CPU resources and to please modify before proceeding. 
 
My question is, why is my query so cumbersome?  Is there anything you can see in the structure of my query that sticks out as terribly inefficient?
 
SELECT
A.USER_ID,
C.SUPER_NM_LVL2,
C.SUPER_NM_LVL1,
DIR.SUPER_NM_LVL2,
A.ACTIVITY_DT,
B.MTN,
A.ACCT_NUM,
A.PPLAN_CD_CURR
FROM DLY_LINE_ACTIVITY_PPLAN_V AS A
INNER JOIN
SUBS_SUM_FACT_V AS B
ON A.ACCT_NUM=B.ACCT_NUM
 
INNER JOIN
HR_EMPLOYEE_V AS C
ON A.USER_ID=C.NT_USER_ID
 
INNER JOIN
HR_EMPLOYEE_V AS DIR
ON C.SUPER_ID_LVL2_EMP_ID=DIR.EMP_ID
 
WHERE
A.ACTIVITY_DT >= '2012-06-28'
--AND A.ACTIVITY_DT <= '2012-11-19'
AND C.EMP_AREA_CD LIKE 'WE'
AND A.PPLAN_CD_CURR IN ('86489', '86491', '86492',
'86494', '86495', '86496', '86497', '86498', '86499', '86500', '86501',
'86502', '86487', '86489', '86504', '86505', '86506', '86507', '86508',
'86509', '86510', '86511', '86512')
 
GROUP BY
A.USER_ID,
C.SUPER_NM_LVL2,
C.SUPER_NM_LVL1,
DIR.SUPER_NM_LVL2,
A.ACTIVITY_DT,
B.MTN,
A.ACCT_NUM,
A.PPLAN_CD_CURR
 

Qaisar Kiani 337 posts Joined 11/05
21 Nov 2012

Explain plan of the query and DDL / Indexes of the table referenced will help in understanding what could be going wrong...

You must sign in to leave a comment.