Temporary tables

Temporary tables

Teradata has implemented three types of temporary tables.

• Derived tables
• Volatile temporary tables
• Global temporary tables

What is the reasons to implement Temporary Tables? 

Temporary tables are an additional tool that assist us in performing SQL operations that either:
  >Might not be possible against a normalized table, or
  >May require multiple SQL statements to complete.

They are some very useful for demoralizations such as:
  >Summary tables.
  >Repeating groups.

And they may also be used for intermediate results which are needed:
  >Frequently, or
  >On an on-going basis.

Derived Tables:
Below are the characteristics of Derived Tables:

• Derived tables are local to the query that means it exists only for the duration of the query.
• The table is automatically discarded once the query is done.
• Spooled rows, which populate the table, are also discarded when query finishes
• It is incorporated into SQL query syntax
• There is no data dictionary involvement 

Below is the example for Derived tables.

SELECT T.PROD_ID, T.SUM_SALES, RANK(T.SUM_SALES)
FROM (SELECT PROD_ID, SUM(SALES) FROM SALES_TBL
         GROUP BY 1) AS T(PROD_ID, SUM_SALES)
QUALIFY RANK(SUM_SALES)<=3; 

In the above query T is the Derived Table.PROD_ID and SUM_SALES are the derived columns.


Volatile Temporary Tables:
Below are the characteristics of Volatile Temporary Tables

• Volatile Temporary Tables are exists throughout the entire session, not just a single query
• It must be explicitly created using the CREATE VOLATILE TABLE syntax. 
• It is discarded automatically at the end of the session or can be dropped manually anytime.
• There is no data dictionary involvement. 
• Can be used with multiple queries in the session.
• Volatile Tables are designed for  performance improvement.
• Can do collect statistics.

Below is the example for Volatile tables.

CREATE VOLATILE TABLE vt_deptsal, LOG
(deptno SMALLINT
,avgsal   DEC(9,2)
,maxsal   DEC(9,2)
,minsal   DEC(9,2)
,sumsal   DEC(9,2)
,empcnt   SMALLINT)
ON COMMIT PRESERVE ROWS; 

ON COMMIT DELETE ROWS means the data is deleted when the query is committed.This statement allows us to use the volatile table again for other queries in the session

Limitations on Volatile Tables:

• CREATE/DROP INDEX
• ALTER TABLE
• GRANT/REVOKE privileges
• DELETE DATABASE/USER (does not drop volatile tables)

Global Temporary Tables:
Below are the characteristics of Global Temporary Tables

• By using CREATE GLOBAL TEMPORARY command we used to create. 
• The main difference between a global temporary table and a volatile temporary table is that the global table definition stored in data dictionar(DD)
• 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 can be possible on Global Temporary tables 
• Secondary indexes can be added to a Global Temporary tables

Below is the example for Global Temporary Tables

CREATE SET GLOBAL TEMPORARY TABLE PED.gt_deptsal
,NO FALLBACK,   LOG
  (
  deptno SMALLINT,
  avgsal DECIMAL(9,2),
  maxsal DECIMAL(9,2),
  minsal DECIMAL(9,2),
  sumsal DECIMAL(9,2),
  empcnt SMALLINT)
PRIMARY INDEX (deptno)
ON COMMIT PRESERVE ROWS; 

The ON COMMIT DELETE ROWS clause is the default.. If you want to use the command ON COMMIT PRESERVE ROWS, you must specify that in the CREATE TABLE statement