Different Types Of Temporary Tables In Teradata

Data Modeling

Different Types Of Temporary Tables In Teradata

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 provides the flexibility to use three types of temporary table which helps user to accomplish their work more easily. This kind of table is temporary to the database that means tables are not going to store permanently in the disk space, will be discarded after specific time based on type of table. 

Types of temporary tables in TeraData are:

1.Global temporary tables

2.Volatile temporary tables

3.Derived tables

Global Temporary Tables(GTT)

  • They exist only for the duration of the SQL session in which they are used.
  • The contents of these tables are private to the session, and System Automatically drops the table at the end of that session.
  • System saves the Global Temporary Table Definition Permanently in the Data Dictionary.
  • The Saved Definition may be Shared by Multiple Users and Sessions with Each Session getting its Own Instance of the Table.

Example of Global table

CREATE GLOBAL TEMPORARY TABLE MYDB.EMPLOYEE( 
EMP_NO VARCHAR(10),
EMP_NAME VARCHAR(10), 
SALARY INTEGER)
UNIQUE PRIMARY INDEX(EMP_NO) 
ON COMMIT PRESERVE ROWS;

Volatile Temporary Tables(VTT)

  • If you need a temporary table for a single use only, you can define a volatile table.
  • The definition of a volatile table resides in memory (RAM) but does not survive across a system restart.
  • It improves performance even more than using global temporary tables because the system does not store the definitions of volatile tables in the Data Dictionary.
  • Access-rights checking is not necessary because only the creator can access the volatile table.

Example of Volatile table

CREATE VOLATILE TABLE EMPLOYEE(    
EMP_NO VARCHAR(10),    
EMP_NAME VARCHAR(10),    
SALARY INTEGER)
UNIQUE PRIMARY INDEX(EMP_NO) 
ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS means to keep the data upon completion of the transaction.

We need to mention ON COMMIT PRESERVE rows explicitly as the default is ON COMMIT DELETE ROWS which delete the data from the table upon completion of the transaction.

Derived Tables(DT)

  • A special type of temporary table is the derived table. It is specified in SQL SELECT statement.
  • A Derived Table is Obtained from One or More Other Tables as the Result of a Sub-Query.
  • Scope of A Derived Table is only Visible to the Level of the SELECT statement calling the Sub-Query.
  • Using Derived Tables avoids having to use the CREATE and DROP TABLE Statements for Storing Retrieved Information and Assists in Coding More Sophisticated, Complex Queries.

Example of Derived table

SEL EMP_NAME,SALARY FROM    EMPLOYEE,(
SEL    AVG(SALARY)
FROM   EMPLOYEE) AS EMPLOYEE_TEMP(AVGSAL)
WHERE SALARY > AVGSAL
ORDER BY 2 DESC;

Here we want to know the employee name whose salary is greater than the average salary. From the above example, you can see that in the from clause, we have calculated average salary of employees. Here EMPLOYEE_TEMP will act a like a derived table. Please note that we need to mention clearly table name and column list clearly in derived table. 

Permanent VS Temporary Tables Permanent and Temporary Tables

  • Permanent storage of tables is necessary when different sessions and users must share table contents.
  • When tables are required for only a single session, we can request the system to create temporary tables.
  • Using this type of table, we can save query results for use in subsequent queries within the same session.
  • We can break down complex queries into smaller queries by storing results in a temporary table for use during the same session. When the session ends, the system automatically drops the temporary table.