All Forums Database
Kishore_1 208 posts Joined 03/10
03 Dec 2010
VTT vs GTT

In which scenarios we use Volatile tables and Global
temporary Tables in Real time?

WAQ 158 posts Joined 02/10
05 Dec 2010

Before that, you must know about the difference between the two. VTT are session scoped whereas GTT is also a session scoped but its DDL can be accessed by other sessions because it is stored in data dictionary.

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.

Based on these differences, you can decide which one fits in which scenario.

Jimm 298 posts Joined 09/07
06 Dec 2010

You would tend to use volatile tables in batch scripts where you are building some form of work or summary table for use only in that script.
Because you can turn logging off for the table, it is much faster if you insert/ select and then go on to add more records or du updates or deletes.
It also simplifies restarting after a failure - there is nothing to clean up for a restart.
GTT are mainly used in very short decision support type queries - you can extract one or a few records and use this to determine further information. For instance, you can find all accounts for a customer, put this in a GTT and then get all the transactions for those accounts.

manoj.d 2 posts Joined 12/10
06 Dec 2010

One correction.

Volatile table allows to create Index. But will not allow named indexes.

Also some thing important. VTT will not collect stats where as GTT will.

ztpruit 4 posts Joined 10/08
02 Jun 2011

I hate to correct the correction but it's not necessarily correct...

Teradata 13 and above will allow you to Collect Stats on Volatile Tables (as well as partitioning columns and Join and Hash Indexes)

td_begnr 2 posts Joined 08/13
28 Aug 2013

i have a question, if VTTs are only session specific, if I create it and load data to it, will another user be able to access the table?

dnoeth 4628 posts Joined 11/04
29 Aug 2013

Well, a VTT is local to your session and other users are connected to different sessions...... ehm... no
 
Dieter

Dieter

Raja_KT 1246 posts Joined 07/09
30 Aug 2013

You can have a better performance with VTT compared with GTT.
Thanks and regards,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
30 Aug 2013

Hi Raja,
an you elaborate on that?
There shouldn't be any difference between GTT and VT.
 
Dieter

Dieter

Raja_KT 1246 posts Joined 07/09
03 Sep 2013

Hi Dieter,
 
In the TD document it is given. Also I see that it is justifiable since, GTT can be  shared across multiple users. So a lookup may be slightly more time-consuming than VT. Anyone having a better explanation?
Thanks and regards,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.