No more spool error- sel * from table sql

General
Enthusiast

No more spool error- sel * from table sql

hello all
My table size is around 150 GB
When I tried to execute a simple sql " select * from the table "
No more spool error occurred
any specific reason !!
Tags (1)
5 REPLIES
Enthusiast

Re: No more spool error- sel * from table sql

Spool space error can come due to multiple reasons.

Are u putting and condition in query ?
Also what is the skew factor for this table?

Also check with admin for the spool space allocated to the user using which u are running this query.

Regards,
Subhash
Enthusiast

Re: No more spool error- sel * from table sql

depending on the requirements spool space of an user is limited to some size. in your case lets assume your spool space size is 100gb and as you said table size is 150 gb.

spool space holds the results of the query. so if your spool space is less than the size of your result set.. you will see the error.......... so you cant sel * from the table with out a condition in the where clause
Teradata Employee

Re: No more spool error- sel * from table sql

Keep in mind that the total spool affected to a user is hold by AMPS, each one has 1/#AMP of the total spool,
if there is a spike on your data distribution, one AMP may raise its local spool limit and fail with "No more Spool..."

Re: No more spool error- sel * from table sql

check the skew factor on this table as this should be the major cause for the error. Also, try to use the PI columns on your where condition.
Further, i am not sure why you want to write select query without any condition. you can try "TOP" instead.

Re: No more spool error- sel * from table sql

see as u r doing sel * from table without giving ne join condition... that would definitely lead you to spool space error..there will be two reasons either your AMP is skewed badly or the spool space assigned to your DB is not sufficent but as you said that your table is 150 GB so probably your spool space is sufficient . Now the appropriate solution is try to check whether you have duplicates on your primary index or put some join condition in the sel query