Teradata - Table Types

Teradata supports the following table types to hold temporary data.

  • Derived Table
  • Volatile Table
  • Global Temporary Table

Derived Table

Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.

Example

The following example builds a derived table EmpSal with records of employees with salary greater than 75000.

SELECT 
Emp.EmployeeNo, 
Emp.FirstName, 
Empsal.NetPay 
FROM 
Employee Emp, 
(select EmployeeNo , NetPay 
from Salary
where NetPay >= 75000) Empsal 
where Emp.EmployeeNo = Empsal.EmployeeNo;

When the above query is executed, it returns the employees with salary greater than 75000.

*** Query completed. One row found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo            FirstName               NetPay 
-----------  ------------------------------  ----------- 
    103                  Peter                 83000

Volatile Table

Volatile tables are created, used and dropped within a user session. Their definition is not stored in data dictionary. They hold intermediate data of the query which is frequently used. Following is the syntax.

Syntax

CREATE [SET|MULTISET] VOALTILE TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 
ON COMMIT [DELETE|PRESERVE] ROWS

Example

CREATE VOLATILE TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no) 
ON COMMIT PRESERVE ROWS;

When the above query is executed, it produces the following output.

*** Table has been created. 
*** Total elapsed time was 1 second.

Global Temporary Table

The definition of Global Temporary table is stored in data dictionary and they can be used by many users/sessions. But the data loaded into global temporary table is retained only during the session. You can materialize up to 2000 global temporary tables per session. Following is the syntax.

Syntax

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions>

Example

CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no);

When the above query is executed, it produces the following output.

*** Table has been created. 
*** Total elapsed time was 1 second.
Advertisements