What is a Teradata Volatile Table?
Volatile tables resemble derived tables but endure beyond the transaction, persisting until the session’s end.
To create a volatile table, use the syntax:
CREATE VOLATILE TABLE Customer ( CustomerId INTEGER NOT NULL, LastName VARCHAR(255) ) PRIMARY INDEX (CustomerId) ON COMMIT PRESERVE ROWS;
Explore an in-depth examination of Volatile Tables, Global Temporary Tables, and Derived Tables below:
Comparison of temporary table types
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 refer 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.
Have you tried adding ‘on commit preserve rows’ command?
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.