Teradata Storage Space

TERADATA has three kinds of Storage Space namely PERMANENT, SPOOL and TEMPORARY. Permanent Space is used for storing permanent date like Permanent Tables, Secondary Indexes and Permanent Journals. So whenever a table is created , it occupies Permanent Space and whenever data is deleted or database objects like tables, indexes are dropped Permanent Space is released. Permanent Space is allocated at the time of creating USER/DATABASE.

Next is SPOOL SPACE. Spool Space is all the space available or we can say un-occupied Permanent Space is called SPOOL Space. Spool Space is used for carrying out all the intermediary SQL operations like creating derived tables or performing some aggregations or storing result set of joins of tables etc. Whenever a SQL query exceeds the SPOOL Space available query is aborted.

Temporary Space is used for storing GLOBAL TEMPORARY TABLES in Teradata. For such tables ,table definition is stored however the table data is truncated once the session is over. Temp space is also un-used Permanent Space.

Tips

Understanding Teradata Storage Space is very essential especially when doing Capacity Planning or Volumetric Estimation in Data Warehouse Environment. Sometimes, it may happen that SQL queries may require more SPOOL SPACE for SQL Operations than available. In such situations knowledge about Space may help.

Related posts: