Volatile Temporary Tables Teradata

Volatile tables were introduced in release V2R3 of Teradata. They have two characteristics in common with derived tables. They are materialized in spool and are unknown in the DD. However, unlike a derived table, a volatile table may be used in more than one SQL statement throughout the life of a session. This feature allows other follow-up queries to utilize the same rows in the temporary table without requiring them to be established again. This ability to use the rows multiple times is their biggest advantage over derived tables.

A volatile table may be dropped manually at any time when it is no longer needed. If it is not dropped manually, it will be dropped automatically at the end of the user session. A user can materialize up to a maximum of 64 volatile tables at a time. Each volatile table requires its own CREATE statement. Unlike a real table with its definition stored in the DD, the volatile table name and column definitions are stored only in cache memory of the Parsing Engine. Since the rows of a volatile table are stored in spool and do not have DD entries, they do not survive a system restart. That is why they are called volatile.

The syntax to create a volatile table follows:

The LOG option indicates the desire for standard transaction logging of "before images" in the transient journal. Without journaling, maintenance activities can be much faster. However, be aware that without journaling, there is no transaction recovery available. LOG is the default, but unlike real tables it can be turned off, by specifying: NO LOG.

The second table option regards the retention of rows that are inserted into a volatile table. The default value is ON COMMIT DELETE ROWS. It specifies that at the end of a transaction, the table rows should be deleted. Although this approach seems unusual, it is actually the default required by the ANSI standard. It is appropriate in situations where a table is materialized only to produce rows and the rows are not needed after the transaction completes. Remember, in ANSI mode, all SQL is considered part of a single transaction until it fails or the user does a COMMIT WORK command.

The ON COMMIT PRESERVE ROWS option provides the more normal situation where the table rows are kept after the end of the transaction. If the rows are going to be needed for other queries in other transactions, use this option or the table will be empty. Since each SQL request is a transaction in Teradata mode, this is the commonly used option to make rows stay in the volatile table for continued use.

Without DD entries, the following options are NOT available with volatile tables:

  • Permanent Journaling
  • Referential Integrity
  • CHECK constraints
  • Column compression
  • Column default values
  • Column titles
  • Named indexes

Volatile tables must have names that are unique within the user's session. They are qualified by the user-id of the session, either explicitly or implicitly. A volatile table cannot exist in a database; it can only materialize in a user's session and area.

The fact that a volatile table exists only to a user's session implies a hidden consequence. No other user may access rows in someone else's volatile table. Furthermore, since it is local to a session, the same user cannot access the rows of their own "volatile table" from another session, only in the original session. Instead, another session must run the same create volatile table command to obtain an instance of it and another SELECT to populate it with the same rows if they are needed in a second session.

Although this might sound bad, it provides greater flexibility. It allows for a situation where the same "table" is used to process different requests by storing completely different rows. On the other hand, it means that a volatile table may not be the best solution when multiple sessions or multiple users need access to the same rows on a frequent basis.

The following examples show how to create, populate, and run queries using a volatile table:

The definition is built in the PE's cache memory. This is the only place that it resides, not in the DD.

The next INSERT/SELECT populates the volatile table created above with one data row per department that has at least one employee in it:

Now that the volatile table exists in the cache memory of the PE and it contains data rows, it is ready for use in a variety of SQL statements:

SELECT * FROM Dept_Aggreg_vt ORDER BY 1; 6 Rows Returnedvariety of SQL statements

The same rows are still available for another SELECT:

2 Rows Returnedsame rows are still available for another SELECT

Whenever a single user needs data rows and they are needed more than once in a session, the volatile table is a better solution than the derived table. Then, as the user logs off, the table definition and spool space are automatically deleted.

Since no DD entry is available for a volatile table, they will not be seen with a HELP USER command. The only way to see how many and which volatile tables exist is to use the following command:

HELP VOLATILE TABLE ; 1 Row Returnedvolatile tables exist is to use the following command

The main disadvantage of a volatile table is that it must be created via the CREATE VOLATILE TABLE statement every time a new session is established. This situation can be overcome using a global temporary table.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics