All Forums Analytics
peterzay 1 post Joined 08/07
08 Aug 2007
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?

Fred 1096 posts Joined 08/04
08 Aug 2007

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.

davidpracy 22 posts Joined 09/05
08 Aug 2007

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.

sachinp17 53 posts Joined 11/06
23 Aug 2007

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

You must sign in to leave a comment.