What is the difference between Global temporary tables and Volatile temporary tables?

Global Temporary tables (GTT) -
1. When they are created, its definition goes into Data Dictionary.
2. When materialized data goes in temp space.
3. thats why, data is active upto the session ends, and definition will remain there upto its not dropped using Drop table statement.
If dropped from some other session then its should be Drop table all;
4. you can collect stats on GTT.

Volatile Temporary tables (VTT) -
1. Table Definition is stored in System cache
2. Data is stored in spool space.
3. thats why, data and table definition both are active only upto session ends.
4. No collect stats for VTT.

Cheers,
Shilpa

Shilpa.bateja
Profile Answers by Shilpa.bateja Questions by Shilpa.bateja
Sep 24th, 2007
4
28340

Questions by Shilpa.bateja

Teradata

Answer

Showing Answers 1 - 4 of 4 Answers

venkat_teradata
Profile Answers by venkat_teradata

Sep 8th, 2008

In addition to the Shilpa's answer,

If you are using volatile table,
you can not put the default values on column level ( while creating table )

krishna

Jul 30th, 2012

There is so much difference between global temporary tables and volatile tables. Try to understand the difference and make comfort the global temporary table the name only defines that temporary so all the temporary data is available in that tables.

it wont show any data only structure will be available.In volatile after session completes not even appear the structure global temporary table,volatile are based upon session derived table is based on query finished nothing will not show

Prashant Nile

Dec 13th, 2013

GTT uses the Temp Space where as Volatile table use the Spool Space.

Both are Session Based once you logged of Data gets vanished.

For GTT Definitions remains as it is, as it gets stored in Data Dictionary, Whereas for Volatile table definition gets vanished once logged off from session.
You can alert the GTt table but you can not alert the Volatile table.

Bikhayat kumar

Feb 10th, 2014

GTT takes up space from temporary space where as VTT takes up space from spool.

GTT survive TD system restarts where as VTT does not.

GTT allows index to be created where as VTT does not.

In a single session 2000 GTT can be materialized where as in case of VTT the count is only 1000.

GTT allows some constraints to be created on where as VTT does not.