Difference between GTT and Volatile tables..!!

Database
Teradata Employee

Difference between GTT and Volatile tables..!!

Hi,

Can anyone let me know some of differences between Global Temp Table & Volatile table and also is there any specific purpose where each can be used???

Thank You,
3 REPLIES
Enthusiast

Re: Difference between GTT and Volatile tables..!!

Quick notes ....

GTT takes up space from Temporary space, so this kind of doesn't interfere with the spool required for your normal queries, also they survive TD system restarts, and since the DDL is stored in DD, you don't have to recreate it in every session that needs to use it, which also means multiple user ids can share the DDL structure (not the data though).

VT takes up space from spool, doesn't have any info in DD, so is slightly faster that GTT in that terms because no DD lookup is required. But it can't survive TD system restarts. The DDL structure is not visible outside that session.

Both are temporary tables and are used normally as a kind of "intermediate stage" for an SQL operation.

In general one could start by considering VT for the task, and then when you look deeper if you see a repeated need of the same DDL structure across multiple sessions/Users (say like an Online system generating some complex reports using a temp table as intermediate) or if your VT stores up a lot of records so that you have the risk of running out of spool space, then you would switch to GTT.

that's my $0.02

There must be some more minor diff between both, which you should be able to find in the DDL/Design manual. but most of the time this would suffice to make a call.
Enthusiast

Re: Difference between GTT and Volatile tables..!!

Global Temporary table definition persist across session and Volatile table are session specific tables.

Index can be created on Global Temporary table and not on Volatile table.

Global temporary table uses Temporary space of login user and Volatile table uses spool space of login user

In a single session 2000 Global temporary table can be materialized and 1000 Volatile tables can be materialized.

CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are supported by Global Temporary table but not by Volatile Table.

So by given the above differences it depends on which one to go if the data is huge and is joined with other large table then i would prefer to go by Global Temporary table.

Fan

Re: Difference between GTT and Volatile tables..!!

when we have to go for these tables?