Temporary Tables in Teradata

Database
Enthusiast

Temporary Tables in Teradata

Hi Gurus,

I am looking for some guidelines on when to use each of the following form of temporary tables:

1) Derived Table

2) Volatile Table

3) Global Temporary Table

Any pointers will really help.

Tags (3)
3 REPLIES
WAQ
Enthusiast

Re: Temporary Tables in Teradata

Here are some of the properties:

1) Derived Table

 - They are specified on a query level with an AS keyword in an sql statement

 - The definition is kept in cache

2) Volatile Table

 - They are dropped when the session ended

 - Created and populated/materialized under user's space (spool)

 - The definition is kept in cache

 - Does not allow index to be created

3) Global Temporary Table

 - Created and populated/materialized under temporary (temp) space

 - The defination is stored in data dictionary hence it can be accessed by other users

 - Survive Teradata system restarts

 - Allows index to be created

Junior Supporter

Re: Temporary Tables in Teradata

Hi.

Just to clarify about GTT's:

>>"The defination is stored in data dictionary hence it can be accessed by other users"

The contents of the (local copies of the) GTT's are private to each session and will disappear after session ends.

Cheers.

Carlos.

WAQ
Enthusiast

Re: Temporary Tables in Teradata

Yes that's correct.

GTT's are created under temporary space. This is the reason why the data in GTT is scoped for the specific session whereas the DDL for GTT can be accessed by other sessions because it is stored in data dictionary.

Thanks Carlos.