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 end of the session.
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;
You can find an in-depth comparison between Volatile Tables, Global Temporary Tables, and Derived Tables 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?
We can only access volatile tables in the user’s session in which we materialize them.
Is there a DBC table where I can see which Volatile Tables I have materialized?
No, but all materialized tables are listed with the command HELP VOLATILE TABLE.
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?
We can’t change the DDL of volatile tables.
Can I create a secondary index on a volatile table?
This is possible when the table is created. Thus, 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?
A volatile table can’t carry a join or hash index.
Who is the owner of a Volatile Table?
A Volatile Table belongs neither to a database nor to a user but to a user’s session.
Can I create the same volatile table in different sessions?
Yes, but the content is session-specific, of course.
Can I use FALLBACK protection?
This is useless because volatile sessions do not survive a system restart.
Can a volatile table be used for performance tuning?
Volatile tables offer several possibilities for performance tuning. E.g., no DBC table is accessed when using a volatile table; Therefore, there can be no congestion as with permanent tables that want to access DBC tables simultaneously.
Can I create statistics on a volatile table?
We can collect statistics on volatile tables. A slight hint: The command DIAGNOSTIC HELPSTATS ON FOR SESSION will recommend statistics on volatile tables even if you have already collected them. The reason is that the information about statistics of volatile tables is not stored in DBC tables.
Can a Volatile Table be created as a PPI table?
Yes, volatile tables can be row-level partitioned.
Can volatile tables be called the same as existing permanent tables?
This is possible, but you must reference the volatile table using USERNAME.TABLENAME; otherwise, you may get an error message if the parsing engine does not know which table you are referring to.
When should I use the NO LOG option for Teradata Volatile Tables?
Predominantly, volatile tables are loaded once (when empty) and only read again. Only when updates, inserts, or deletes happen on the already loaded table the NO LOG option is helpful to prevent the transient journal from being written because a rollback of the changes on a volatile table is mostly unnecessary.
Are volatile tables block-level compressed?
If the optimizer keeps the table in the request cache, it is not compressed. Otherwise, it is.
Teradata Volatile Table Restrictions
- No referential integrity can be defined
- No CHECK constraints are allowed
- DEFAULT values are not allowed
- We can define no TITLE for columns
- No Join Index or Hash Index is allowed
- We can’t name indexes
- We can’t use permanent Journals
- Column Partitioning is not allowed
- No privileges can be set with a GRANT or REVOKE. That wouldn’t make any sense either.