View vs volatile table?

Question

What is the difference between a view and a volatile table in Teradata? As far as I know, a volatile table is removed with the end of the session. Also it is only me who can see the volatile table as opposed to the view. Are there any other significant differences?

Solution 1

A volatile table stores the data physically. You can access that data multiple times during your session. With a View the data is collected every time you access it.

To help speed up queries on views, you can use Join Indexes on Teradata. They physically store the results of a certain select and maintain it when the underlying data is changed (insert, update, delete) automatically - like most of things on a Teradata.

Solution 2

A volatile table is a temporary table that is only held until the end of session. This is created by default in your "personal schema" and consumes your spool space to maintain.

A view is an object that is permanent across sessions, generates from tables existing in the environment you are in, and does not consume spool space continuously.

Generally speaking I use volatile tables to upload from scripts so that I can process information and drop table easily at the end of a session. I use views to change the way a user sees information that resides in other tables or to restrict access to information to certain users without altering root tables.

Comments

[Rob Paller]

In Teradata the Volatile Table is temporarily instantiated during the users session and the user's spool space is used to store the data within the table.