Monday, October 5, 2015

Types of spools in Teradata

Teradata Database draws spool space dynamically from unused system perm space, there are mainly three types of spool usage in the system.

Volatile Spool:
Volatile spool is used for volatile table creation and  is retained until the Transaction completes (unless the table was created with ON COMMIT PRESERVE ROW)
or Table is dropped manually during the session
or Session ends
or Teradata Database resets

Intermediate Spool:
Intermediate spool is used during query processing, the spool are retained until they are no longer needed by the query for which they were created. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN.
Note: The first step performed after intermediate spool has been flushed is designated “Last Use.”
or Teradata Database resets

Output Spool:
Output spool is used mainly for returning the result rows while responding, these are either Response rows returned in the answer set for a query or during Rows updated within, inserted into, or deleted from a base table