Spool space problems occur either when you have an inefficient query or when statistics have not been properly collected on the tables you are using. It can also happen with tables where the primary index was poorly chosen (high skew). Spool is an attribute of the user account you are using to connect to the Teradata environment; it is not really an attribute of the database itself.
The only way to know for certain is to look at the EXPLAIN plan for your query.
If your query is inefficient, rewrite it. If statistics need to be collected or if the index needs to be altered, contact the DBA responsible for the tables you are using.
If there is a particular query that is giving you an «out of spool» error, update this question with the complete text of the query.
My error is no more spool space when I create a table with the qualify number_rows,
the goal is to get the first 100 samples by key,
each key is composed by the following fields: (top_typ_vision, instid, don_gener3, don_gener4,rg_no, lieu_stkph_cd,id_sect_base_resp)
When I execute the select, the code works very well, once I add the create I get the error no more spool space
thank you !!
```sql
create multiset table mdc_cobalt_det as (
sel
top_typ_vision,
instid,
type_enr as type_obj_ofs,
don_gener1,
don_gener2,
don_gener3,
don_gener4,
rg_no,
lieu_stkph_cd,
id_sect_base_resp
from PROD_V_CTRL_ANOMALIE
qualify row_number () over (partition by top_typ_vision,
instid,
don_gener3,
don_gener4,
rg_no, lieu_stkph_cd,
id_sect_base_resp order by rg_no ) <= 100)
with data
primary index (top_typ_vision, rg_no, don_gener3, don_gener4, lieu_stkph_cd, id_sect_base_resp);
Last Modified Date: 24 Aug 2022
Issue
When refreshing an extract from a Teradata connection, the extract fails with the following message:
Environment
- Tableau Server
- Teradata extract
Resolution
To resolve this issue, please work with your Teradata database administrator to investigate the following workarounds:
- Gather statistics on the underlying tables used in the extract.
- Consider increasing spool space in Teradata.
Cause
- Tableau is attempting to run a query against tables that do not have latest Stats gathered.
- Spool space is too small to handle the query Tableau is sending to Teradata.
Have you ever come across a situation called NO MORE SPOOL SPACE? My friend does and hence I studied a bit about Teradata.
# The query:
SELECT DISTINCT fieldname FROM tablename;The error:
Correct, the problem wasn’t related to MySQL, that was for something called Teradata.
Initially, I could see that query should be using temporary space (from name SPOOL SPACE I guessed) to get distinct data – may be it’s a huge table or something.
So I think one has to look into temporary table space’s size – they call it SPOOL SPACE.
Next is Google’s turn to explain me more, I Googled with appropriate keywords and got following understandings:
Spool is temporary disk space used to hold intermediate rows during query processing, and to hold the rows in the answer set of a transaction.
Spool space is allocated to a user as a total amount available for that user, however this total amount is spread across all AMPS. This means if you’re allocated 200G of spool and you’re on a system with 24 AMPS, you’re allocated ~8.3G of spool on each AMP. If any AMP exceeds its allocated amount of spool during a transaction you submit, your query is cancelled with error: NO MORE SPOOL SPACE.
Common reasons for “No More Spool Space Error”:
1) All the rows in the query are being processed on a few amps creating a “hot amp” situation. In this case, just a few amps are racking up spool while the others sit comparatively idle. This is caused when the tables in the query are missing stats, have been improperly PI’d, or are otherwise “untuned”.
2) The query is excluding critical join criteria resulting in Cartesian products.
3) You just don’t have enough space to perform the query.
From other readings I suggested one query change:
SELECT fieldname FROM tablename GROUP BY fieldname;The reason behind above query is:
Teradata uses two different methods to produce the result sets of above queries, However the performance varies dramatically depending on the data.
SELECT DISTINCT is designed for data sets that are nearly unique to begin with and works by sorting the entire intermediate spool file and discarding duplicate values.
SELECT/GROUP BY is designed for data sets that have relatively few unique values and works by performing an AMP local grouping operation and then merging the partial result sets for final processing.
Few other steps (even) I can suggest to solve such problem are:
– Explain to see execution plan to have an idea.
– Check table definition for indexes: Show table table-name;
– CREATE INDEX indexName (field-name) on table-name;
Finally, if nothing happens contact your DBA or the “right person” 🙂
I hope this will help someone, somewhere, someday.
If you know that I’ve misunderstood something please comment and give respective links – I should be correcting it asap.
Related Tags
- no more spool space,
- query,
- teradata
Loading…