CREATE GLOBAL TEMPORARY TABLE in Teradata

GTT is a temporary table used normally as a kind of "intermediate stage" for an SQL operation

Syntax:

CREATE MULTISET GLOBAL TEMPORARY TABLE DB.GTT_EMP_STATS ,NO FALLBACK,   LOG
  (
  deptno SMALLINT,
  avgsal DECIMAL(9,2),
  maxsal DECIMAL(9,2),
  minsal DECIMAL(9,2),
  empcnt SMALLINT)
PRIMARY INDEX (deptno)
ON COMMIT PRESERVE ROWS; 

Note:

• Global temporary table definition is stored in the data dictionary. so you only have to create it once and reuse it ever after
  
• Each instance of a global temporary table is local to a session
  
• Materialized tables are dropped automatically at the end of the session
  
• A user can materialize up to 2,000 global tables per session.
  
• COLLECTing STATISTICS is possible on Global Temporary tables
  
• Secondary indexes can be added to a Global Temporary tables
  

nVector

posted on 14 May 18