VTT vs GTT

Database
Enthusiast

VTT vs GTT

In which scenarios we use Volatile tables and Global
temporary Tables in Real time?
9 REPLIES
WAQ
Enthusiast

Re: VTT vs GTT

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

Re: VTT vs GTT

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

Re: VTT vs GTT

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.

Re: VTT vs GTT

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)

Re: VTT vs GTT

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?

Senior Apprentice

Re: VTT vs GTT

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

Dieter

Enthusiast

Re: VTT vs GTT

You can have a better performance with VTT compared with GTT.

Thanks and regards,

Raja

Senior Apprentice

Re: VTT vs GTT

Hi Raja,

an you elaborate on that?

There shouldn't be any difference between GTT and VT.

Dieter

Enthusiast

Re: VTT vs GTT

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