In Teradata, we can create several kinds of Tables ; one of them is VOLATILE Tables. Volatile tables are used to store some data which is session specific and we need not store that data persistently. Once the session is over, all the data and table definition is lost. Volatile Tables use SPOOL SPACE. We can create a VOLATILE table by using below minimal syntax:

CREATE VOLATILE TABLE TABLE_NAME
(
COLUMN_1 DATATYPE,
COLUMN_2 DATATYPE
) PRIMARY INDEX (COLUMN_1)
ON COMMIT [PRESERVE/DELETE] ROWS;

Few Options which you should consider while creating Volatile Tables:

LOG/NO LOG:

If Volatile table is created with LOG option, then it will be using Transient Journal to save “BEFORE IMAGES” of all the transactions. If it will add to data security but will impact the performance. If the transaction is not that complex, then you should go ahead with NO LOG option. Default is LOG option.

ON COMMIT [PRESERVE/DELETE] ROWS:

This is very important option and most of the time you will be using PRESERVE option. If PRESERVE option is kept then Volatile Table will store the data after each transaction however if the DELETE option is kept then it will lose the data once the transaction is completed.
Options NOT available for VOLATILE Tables:

  • Permanent Journals
  • Foreign Key
  • Compression at Column Level
  • Default Values
  • Name of Indexes

Example:

CREATE VOLATILE TABLE SALARY_STATS ,
NO LOG
(
HIGHEST_SAL INTEGER,
AVG_SAL INTEGER,
LOWEST_SAL INTEGER
)
PRIMARY INDEX (HIGHEST_SAL)
ON COMMIT PRESERVE ROWS;

Once the session is over, all the data and table definition is lost and is never saved in Data Dictionary. Volatile Tables used Spool Space and once session is over , the occupied Spool space is again available for some other transactions.

5 Comments

  1. Hi any one please suggest how to find volatile table size

    1. Author

      You cannot find the exact size of a volatile table. However you can check the spool space used by a session or user.


  2. SRC TGT
    JHON KENNDY .K.JHON
    ROBIN SINGH S.ROBIN
    PAUL WALKER W.PAUL

    HOW IT POSSIBLE?

  3. PLZ SEND ME INTERVIEW QUESTIONS ON SQL ASSISTANCE SCENARIOS AND TERADATA

Leave a Reply

Your email address will not be published. Required fields are marked *