Teradata Volatile Tables – The Characteristics

These are the main characteristics of Teradata Volatile Tables:

  • Teradata Volatile Tables only exist in the creating session (for example, a session in the SQL Assistant).
  • Whenever a session is closed, all volatile tables are removed; It’s also possible to remove volatile tables earlier, by executing a DROP TABLE statement.
  • The DDL of a volatile table is not stored in the Teradata Data Dictionary (i.e. the DBC tables), but cached on each AMP as long as session and table exist.By using volatile tables, the blocking of the DBC tables can be avoided; This is an advantage over permanent tables: Whenever the DDL of a permanent table is changed, created, or dropped, a write lock on the related DBC tables is required, possibly causing blocking issues. This may impact performance, especially on systems running a lot of concurrent workload.
  • A volatile table is available for all queries in the same session, but it is invisible to any other session.
  • Statistics can be collected on volatile tables just like for permanent tables.
  • Volatile tables can be row partitioned.
  • Volatile Tables are created in the spool space of the user the session belongs to. No permanent space is needed.

Here is an example of a volatile table DDL:

column_name INTEGER

The clause “ON COMMIT PRESERVE ROWS” could be left out, but it’s basically always required: If it’s left out, all inserted rows are deleted immediately after the insert statement terminates.

There are several limitations applying to Teradata Volatile Tables:

  • No index can be created on top of a volatile table (which results from the fact, that the DDL is not stored in the data dictionary).
  • Volatile tables cannot be altered (ALTER TABLE statement).
  • As only the user who created the session has access to the volatile table, no privileges can be set (REVOKE, GRANT permissions).
Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 15 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

  • Avatar Paul Bartells says:

    I am having issues with accessing a volatile table within the same session. I can successfully CREATE a multiset volatile table, but when I try to import a text table using an INSERT statement, I am told the volatile table doesn’t exist. My task is run in three operations. First, the table definition is created using CREATE MULTISET VOLATILE TABLE cdwhist. Then, I turn on IMPORT DATA and run the INSERT INTO cdwhist process. Finally, the last step utilizes the cdwhist volatile table and joins it with a table on the database. It is the second step that’s failing.

    Code is available, if needed.

  • Avatar Linda says:

    Have you tried adding ‘on commit preserve rows’ command?

  • >