In which scenarios we use Volatile tables and Global
temporary Tables in Real time?
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.
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.
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.
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)