Teradata Volatile Tables – How To Use Them
What is a Teradata Volatile Table?
Volatile tables are very similar to derived tables. In contrast to these, their lifetime is not limited to the duration of a query, but they exist until the end of the session in which they were created.
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;
A comparison between Volatile Tables, Global Temporary Tables, and Derived Tables can be found here:
Teradata Volatile Table Features
Volatile Tables are materialized in the spool space of the user.
Volatile tables can only be accessed in the user's session in which they were materialized.
No, but with the command HELP VOLATILE TABLE, all materialized tables are listed.
The table definition is kept in the AMP cache.
Volatile tables are automatically removed when a session is terminated or a DROP TABLE statement is executed.
Unfortunately, the table is lost along with its contents.
This is not possible.
This is only possible when the table is created. Therefore, you cannot first create the table and then execute a CREATE INDEX statement.
No, that's not possible.
A Volatile Table belongs neither to a database nor to a user, but to the session of a user.
Yes, but the content is session-specific, of course.
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.
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.
Statistics are allowed, but when using the statement DIAGNOSTIC HELPSTAT ON FOR SESSION you 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.
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