All Forums Database
idg_tddevex 18 posts Joined 10/06
23 May 2007
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,

joedsilva 505 posts Joined 07/05
23 May 2007

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.02There 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.

vinod_sugur 22 posts Joined 04/05
23 May 2007

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 userIn 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.

vishu 2 posts Joined 04/13
26 Apr 2013

when we have to go for these tables?

You must sign in to leave a comment.