Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 11 Mar 2008 @ 16:08:59 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Spool space problem & questions
From:   Michael Larkins

Hello Veda:

As indicated by Victor, you probably need to get better acquainted with the architecture of Teradata through reading documentation, books on the subject or through classes.

To provide a quick reply to some of your questions:

  The resultant set is transferred to spool1/spool2....  

These are simply temporary names used to identify spool usage. Data is normally returned to the user from SPOOL1 working down as other steps are needed to create SPOOL1.

  2. Select top 1000 * from xyz;  

Teradata only puts 1000 rows into SPOOL1. They might all come from a single AMP if it has that many rows, or it might be required to go to multiple AMPs to retrieve these rows based on the distribution of rows within this table across the AMPs. If you were to set an output limit in BTEQ, Nexus or SQL Asst the rows are selected into spool first and then limited as they are returned to the client. This is not how TOP works.

  3. I have written a query to retrieve the records but it took 1 hour after that it shows an error as insufficient spool space. (I have assigned enough spool space)  

Since you do not indicate anything about what you are attempting to accomplish, there is no way to know why it did not work. If you hope for a reasonable answer, you need to provide a reasonable explanation of what you are doing (SQL wouldn't hurt). Please realize that spool is evenly distributed across all AMPs. Therefore, it you have 100MB of spool assigned to your user and 100 AMPs, you really have 1MB of spool on each AMP. If your query redistributes data using a join, DISTINCT or GROUP BY, you could exceed the 1MB on a single AMP and therefore you are out of space and your get a spooling error. So, if you have a 100MB requirement for the entire answer set, you might have a 2MB requirement on one AMP. As a result, your user could need 200MB of spool assigned for certain queries. Without this amount, you do not have sufficient spool space assigned.

Welcome to the forum and regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016