spool error - while running query with more than one aggregate functions

Database
N/A

spool error - while running query with more than one aggregate functions

Getting "No more spool space" error while running the following oracle query in teradata

SELECT PC_CD, PD_CD, PA_CD, PO_CD, PS_CD, PT_CE,
SUM(CC_10) CC_10, SUM(CC_30) CC_30, SUM(CC_120) CC_12,SUM(EE_10) EE_10,
SUM(ET_30) ET_30, SUM(EH_120) EH_120,
AVG(BG_1A) BG_1A, AVG(BG_3A) BG_3A, AVG(BG_12A) BG_12A
FROM ESAL_MV
GROUP BY PC_CD, PD_CD, PA_CD, PO_CD, PS_CD, PT_CE

Is there any work around to rewrite this query in teradata to get same result set as in oracle without increasing the spool space
3 REPLIES

Re: spool error - while running query with more than one aggregate functions

I see the query is is a single table retreive but with lot of aggregations which might require redistributions in spool.Check to see the spool space limits for the user running this query and also ensure there is enough free space available.
N/A

Re: spool error - while running query with more than one aggregate functions

Thank you very much 'Leo' for you quick response. We have a restriction of the spool space for the user and can you please let me know, how to carry out the redistributions in spool.

Many Thanks,
Sony

Re: spool error - while running query with more than one aggregate functions

distributions ,Redistributions and aggregations are internally handled by teradata and user do no have any control in such activity.a user can read the explain of his query and try to tune his queries to minimize any redundent redistributions that might have occured due to various reasons (improper index choices,aggregations,grouping etc.)

Spool Space limits are imposed to restrict user to use only that amount of available free space owned by him or immediate parent as set by spool space limit.However, sometime even though the limits were set to higher values there might be a situation where there is not enough free space for this user.