Teradata temporary tables
Teradata database provides various options in case of a need to use temporary tables. The temporary tables are especially useful when performing complicated calculations, they usually make multiple, complex SQL query simpler and increase overall SQL query performance.
Temporary tables are especially useful for reporting and performing operations on summarized values.
Teradata temporary table types:
Derived temporary tables
- Derived tables are local to an SQL query.
- Not included in the DBC data dictionary database, the definition is kept in cache.
- They are specified on a query level with an AS keyword in an sql statement
Volatile temporary tables
- Local to a session ( deleted automatically when the session terminates)
- Not included in the DBC data dictionary database and table definition is stored in cache. However, the volatile tables need to have unique names across the session.
- Created by the CREATE VOLATILE TABLE sql statement
Global temporary tables
- Global tables are local to a session and deleted automatically when the session terminates
- A permanent table definition is stored in the DBC data dictionary database (DBC.Temptables)
- Defined with the CREATE GLOBAL TEMPORARY TABLE sql