SQL and spool space

Analytics

SQL and spool space

An SQL SELECT .... GROUP BY ... failed because it had more variables in the select list than in the group by list. It was recoded to use a correlated query but failed on spool space.

Due to constraints, I can't get more spool space.

Is there a prefix option I can embed into the SQL to get TD to run the original query? If not, is there some option I can embed into the correlated query that will "just make do" with the available spool space?
3 REPLIES
Teradata Employee

Re: SQL and spool space

You are approaching this from the wrong point of view.

No, there is no option to "run my query even though it's syntactically incorrect".

And the spool space requirement is a function of the data and the access plan. There's no way to run that same sequence of steps against the same data but in less space.

But if you tune the query (EXPLAIN, collect appropriate statistics where needed, possibly even rewrite the SQL using some additional knowledge you have that the database does not), then maybe you can help the optimizer find a different access plan that requires less spool.

Re: SQL and spool space

What can't you added the extra fields to the group by?

Have you checked that the query isn't skewed - this could cause you to run out of spool.

Re: SQL and spool space

Hi,
Please try the using the same query with "where" clause so that the query will return around 1000-10000 row. If its successful then go for tunning.

Regds,
Sachin