Teradata Volatile Table Syntax with Example

volatile table in teradata

Volatile tables are session-specific tables which stores intermediate data and once the session is over, all the data and table definition is removed. Volatile Tables uses SPOOL SPACE. In Teradata, use keyword "VOLATILE" in CREATE statement to create a volatile table.

Teradata VOLATILE syntax

CREATE VOLATILE TABLE TABLE_NAME
 (
 COLUMN_1 DATATYPE,
 COLUMN_2 DATATYPE
 ) PRIMARY INDEX (COLUMN_1)
 ON COMMIT [PRESERVE/DELETE] ROWS;

Options/Settings for Volatile Tables:

LOG/NO LOG:

If Volatile table is created with LOG option, then it will use Transient Journal to save "BEFORE IMAGES" of all the transactions. Since the data is intermediate and can be generated again in case of failures I strongly recommend to go ahead with NO LOG option. This is one simple thing you can do to improve performance of volatile tables. Default is LOG option.

ON COMMIT [PRESERVE/DELETE] ROWS:

This is very important option and most of the time you will be using PRESERVE option. If PRESERVE option is kept then Volatile Table will store the data after each transaction however if the DELETE option is kept then it will lose the data once the transaction is completed. Default is "ON COMMIT DELETE ROWS" hence by default volatile table retains data at transaction level and not session level. So you must make it "ON COMMIT PRESERVE ROWS"

Options NOT available for VOLATILE Tables:

1. Permanent Journals
2. Foreign Key
3. Compression at Column Level
4. Default Values
5. Name of Indexes

Volatile Table Example

CREATE VOLATILE TABLE SALARY_STATS ,
NO LOG
 (
 EMP_ID INTEGER,
 HIGHEST_SAL INTEGER,
 AVG_SAL INTEGER,
 LOWEST_SAL INTEGER
 )
 PRIMARY INDEX (HIGHEST_SAL)
 ON COMMIT PRESERVE ROWS;

Once the session is over, all the data and table definition is lost and is never saved in Data Dictionary. Volatile Tables used Spool Space and once session is over , the occupied Spool space is again available for some other transactions.

VOLATILE table with DATA

In most of the ETL use-cases you will create a volatile table from another existing table. If you are creating a table with another table and you want to get records then you have to use "WITH DATA" clause in volatile table CREATE statement. Also if you want to specify different primary index then you can mention it after "with data" clause.

teradata create volatile table as select

CREATE VOLATILE TABLE SALARY_STATS 
 AS 
 (
 SELECT EMP_ID ,
 max(salary) AS HIGHEST_SAL ,
 avg(salary) AS AVG_SAL ,
 min(salary) AS LOWEST_SAL 
 FROM employee
 GROUP BY emp_id
 )
 WITH DATA 
 PRIMARY INDEX (HIGHEST_SAL)
 ON COMMIT PRESERVE ROWS;

To check all the volatile table created in current session use below command:

HELP VOLATILE TABLE;

Teradata Volatile Table vs Global Temporary Table

1. Both the Volatile & Global Temporary table retains session-specific data only. Once session completes data is removed from table.
2. Global Temporary table retains table definition permanently. However for volatile DDL is removed too. So you can use Temporary table in another session without the need to create it all the time.
3. Volatile table use SPOOL space where as Temporary table use TEMPORARY space (basically un-used Permanent Space).
4. In earlier version , collect stats on Volatile was not supported but now it is for both kind of tables. For Temporary table, use keyword "TEMPORARY" before tablename in collect stats.