What is a Teradata Volatile Table?
Volatile tables are very similar to derived tables, but their existence is not limited to their transaction; they stay materialized until the sessions in which they have been created ends.
Volatile tables are created with the following syntax:
CREATE VOLATILE TABLE Customer ( CustomerId INTEGER NOT NULL, LastName VARCHAR(255) ) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;
An in-depth comparison between Volatile Tables, Global Temporary Tables, and Derived Tables can be found here:
Teradata Volatile Table Features
What kind of space does a Volatile Table use?
Volatile Tables are materialized in the spool space of the user.
Who can access a Volatile Table?
Volatile tables can only be accessed in the user’s session in which they were materialized.
Is there a DBC table where I can see which Volatile Tables I have materialized?
No, but with the command HELP VOLATILE TABLE, all materialized tables are listed.
Where is the DDL of a volatile table stored if not in the Data Dictionary (DBC)?
The table definition is kept in the AMP cache.
How long is a volatile table available?
Volatile tables are automatically removed when a session is terminated or a DROP TABLE statement is executed.
What happens if Teradata is restarted while I have materialized a volatile table?
Unfortunately, the table is lost along with its contents.
Can an ALTER TABLE be executed on a volatile table?
This is not possible.
Can I create a secondary index on a volatile table?
This is only possible when the table is created. Therefore, you cannot first create the table and then execute a CREATE INDEX statement.
Can I create a Join Index or Hash Index on a volatile table?
No, that’s not possible.
Who is the owner of a Volatile Table?
A Volatile Table belongs neither to a database nor to a user, but to the session of a user.
Can I create the same volatile table in different sessions?
Yes, but the content is session-specific, of course.
Can I use FALLBACK protection?
Yes, but this is not really useful because volatile sessions do not survive a system restart. It only makes sense to create a volatile table while an AMP is down.
Can a volatile table be used for performance tuning?
Volatile tables offer several possibilities for this. E.g., no DBC table is needed, and therefore, there can be no performance degradation if the competing workload on one of these tables holds e.g. a wide lock.
Can I create statistics on a volatile table?
Statistics are allowed, but when using the statement DIAGNOSTIC HELPSTAT ON FOR SESSION, we have to be aware that they are still proposed in the EXPLAIN plan. This is because they are not available to the optimizer in a DBC table.
Can a Volatile Table be created as a PPI table?
Yes, this is possible.
Teradata Volatile Table Restrictions
- No referential integrity can be defined
- No CHECK constraints are allowed
- DEFAULT values are not allowed
- No TITLE can be defined for columns
- No Join Index or Hash Index is allowed
- Indexes can’t be named
- Permanent Journals can’t be used
- Column Partitioning is not allowed
- No privileges can be set with a GRANT or REVOKE. That wouldn’t make any sense either