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
This comment has been removed by the author.
ReplyDeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Hats off to your presence of mind..I really enjoyed reading your blog. I really appreciate your information which you shared with us.
ReplyDeleteOracle course in chennai
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.
ReplyDeleteTop Web Design Training India
Thanks for sharing the steps to write an e-commerce blog. I think this is very useful especially for beginners.
ReplyDeleteAll those who are into e-commerce and wants to know more about it and re-commerce.
mobile repairing training institute in hyderabad
Great info. I love all the posts, I really enjoyed,
ReplyDeletenice post and site, good work!
I would like more information about this, because it is very nice.
java training in hyderabad