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
) PRIMARY INDEX (COLUMN_1)
ON COMMIT [PRESERVE/DELETE] ROWS;
Few Options which you should consider while creating Volatile Tables:
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
CREATE VOLATILE TABLE SALARY_STATS ,
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.