Tuesday, February 15, 2011

What are the difference types of temporary tables in Teradata?


What are the difference types of temporary tables in Teradata?
Answers:

a.       Global temporary tables
b.       Volatile temporary tables
c.       Derived tables

Global Temporary tables (GTT) –
1. When they are created, its definition goes into Data Dictionary.
2. When materialized data goes in temp space.
3. That's why, data is active up to the session ends, and definition will remain there up-to its not dropped using Drop table statement. If dropped from some other session then its should be Drop table all;
4. You can collect stats on GTT.
5. Defined with the CREATE GLOBAL TEMPORARY TABLE sql


Volatile Temporary tables (VTT) -
1. Local to a session (deleted automatically when the session terminates)
2. Table Definition is stored in System cache .A permanent table definition is stored in the DBC data dictionary database (DBC.Temptables) .
3. Data is stored in spool space.
4. That’s why; data and table definition both are active only up to session ends.
5. No collect stats for VTT.If you are using volatile table, you can not put the default values on column level (while creating table)
6. Created by the CREATE VOLATILE TABLE sql statement

Derived tables
1 Derived tables are local to an SQL query.
2 Not included in the DBC data dictionary database, the definition is kept in cache.
3 They are specified on a query level with an AS keyword in an sql statement

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hats off to your presence of mind..I really enjoyed reading your blog. I really appreciate your information which you shared with us.

    Oracle course in chennai

    ReplyDelete
  3. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating.
    Top Web Design Training India

    ReplyDelete
  4. Thanks for sharing the steps to write an e-commerce blog. I think this is very useful especially for beginners.
    All those who are into e-commerce and wants to know more about it and re-commerce.
    mobile repairing training institute in hyderabad

    ReplyDelete
  5. Great info. I love all the posts, I really enjoyed,
    nice post and site, good work!
    I would like more information about this, because it is very nice.
    java training in hyderabad

    ReplyDelete