8

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?

Azamat Bagatov
  • 289
  • 3
  • 11
  • 26

3 Answers3

6

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.

DrBailey
  • 183
  • 1
  • 9
2

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.

jboi
  • 11,324
  • 4
  • 36
  • 43
  • 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. – Rob Paller Nov 01 '13 at 01:52
0

Also the volatile table holds the data in it for the session. If the data in the source-table changes, it does not automatically change it in the volatile table. View would automatically include the changes as it simply re-runs the SQL on the source-tables.

Kimmo
  • 1
  • 1