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:
CREATE VOLATILE TABLE table_name
) ON COMMIT PRESERVE ROWS;
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).