Teradata Temporary Tables: A Guide to Derived and Volatile Tables

Introduction to Teradata Temporary Tables

This article highlights the distinct types and uses of Teradata temporary tables.

1. Derived Tables

• Are materialized by a select statement within a query
• Only exist within the time the query is executed
• Use the spool space of the executing user
• Disappear at the moment the query is finished

To avoid syntax errors, tables derived from queries should always be named alias.

Here is a derived table example (in parentheses):

SELECT * FROM (SELECT MAX(Revenue) FROM Invoices) AS InvoiceRevenue (MaxRevenue);

The table is labeled InvoiceRevenue, and the derived table’s column is named MaxRevenue.

There are additional options for labeling the column in the resulting table:

SELECT * FROM (SELECT MAX(Revenue) AS MaxRevenue FROM Invoices) AS InvoiceRevenue;

In the above example, we name the column in the derived table.

The choice of method is subjective as the query output remains identical.

Derived Tables using the WITH Syntax

One way to assign a name to a derived table and its columns beforehand and subsequently refer to it is as follows:

WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * FROM  InvoiceRevenue; 

Consider that queries allow for only one WITH statement.

This Syntax has the advantage of a pre-defined table that can be effortlessly incorporated into the query.

Here is an example:

WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * 
FROM 
  InvoiceRevenue t01 
INNER JOIN 
  Customer t02 
ON 
  t01.CustomerId = t02.CustomerId; 

It’s crucial to comprehend that the resulting table is solely present during query execution and not for the complete session.

Below are two queries executed in a single transaction, but the second query triggers an error message:

BT;
WITH InvoiceRevenue(MaxRevenue) AS  
(SELECT MAX(Revenue) AS MaxRevenue FROM Invoices)
SELECT * 
FROM 
  InvoiceRevenue t01 
INNER JOIN 
  Customer t02 
ON 
  t01.CustomerId = t02.CustomerId; 

SELECT * FROM  InvoiceRevenue; --> causes an error
ET;

2. Volatile Tables

• Are created and afterward materialized by adding data with an INSERT/SELECT statement or
• Are created and materialized at the same time with a CREATE VOLATILE TABLE TheTable AS (<QUERY>) WITH DATA PRIMARY INDEX (PI) ON COMMIT PRESERVE ROWS statement
• Use the spool space of the executing user.
• Disappear at the moment the session is logged of
• The table definition is stored in each AMP’s cache

CREATE VOLATILE TABLE Revenue, NO LOG
(
   CustomerId BIGINT NOT NULL,
   RevenueAmount DECIMAL(18,3)
) ON COMMIT PRESERVE ROWS;

NO LOG means that no Transient Journal is used.

Since the table is temporary, the need for ROLLBACK functionality is typically unnecessary.

The default setting is NO LOG, and it is generally recommended to maintain this setting.

The setting of ON COMMIT PRESERVE ROWS is non-default and requires explicit specification. If not set, the transaction’s conclusion results in the prompt deletion of the table’s contents.

The default is to delete rows upon commitment.

Once the volatile table is created, it can be loaded like a standard table.

INSERT INTO Revenue
SELECT CustomerId, SUM(Revenue) FROM Transaction;

Why do I need ON COMMIT DELETE ROWS?

Here is an example of how to apply ON COMMIT DELETE ROWS practically.

ON DELETE COMMIT ROWS removes data immediately upon transaction completion.

We can leverage this opportunity to eliminate a transient table once it becomes dispensable promptly.

BT;
CREATE VOLATILE TABLE Revenue, NO LOG
(
   CustomerId BIGINT NOT NULL,
   RevenueAmount DECIMAL(18,3)
) ON COMMIT DELETE ROWS;

INSERT INTO Revenue SELECT CustomerId,AVG(Revenue) FROM Transaction;

SELECT * FROM Revenue; 
-- The volatile table still exists as the transaction is not ended!

ET; -- Now the volatile table is empty

-- Further Queries...

Additional possibilities to materialize volatile tables

How can I copy Statistics to the Volatile Table?

CREATE VOLATILE TABLE Revenue AS Revenue_All WITH DATA AND STATISTICS ON COMMIT PRESERVE ROWS;

How do I find all available volatile tables in my session?

Execute this command:

HELP VOLATILE TABLE;

3. Global Temporary Tables

• They are created with a CREATE TABLE DDL, which is stored permanently in DBC tables.
• Are materialized with an INSERT/SELECT statement
• Use the temp space of the executing user (not the spool space)
• Disappear when the session is logged off (but the table definition stays stored in the DBC tables!)
• Each user can materialize their copy of a Global Temporary Table

Global temporary tables are useful for sharing a definition among multiple users.

The Global Temporary Table is similar to a Volatile Table in that it exists only in the session where it was created. However, it differs because multiple users can create this table as the definition is stored permanently in DBC tables.