All Forums Database
prateek.saxena 12 posts Joined 08/13
01 Apr 2014
Volatile table vs views.

Can some one please tell me, between Volatile table and view, which is better in terms of efficiency, accessbility and use?
What is the difference between them??
Also, can I create a volatile table with same name but using different user ID?
I have a code which has 120-125 intermdeiate table, I want to replace them with volatile table/view so that I can hit the same code parallely using different user ID.

Regards, Prateek
Santanu84 122 posts Joined 04/13
02 Apr 2014

Hi Prateek
Let me try to answer your question.
Volatile table and views they are different by nature. Volatile tables are session specific, they use spool space from user. So with different userids you can create more than one volatile tables with same name. But once the session is over the volatile table will be dropped. Also the definition of volatile table is not saved in dictionary.
On the other hand a view is a definition saved in the database. There should be a base table related to the view. metarialized view is still not supported. When you access view it eventually access the base table created using perm space.
What is your code actually doing? Do you need these intermediate tables to hold data for further processing before loading into target table?
Based on the usage of volatile table and views try to figure what will best for you. 
I am sure experts in this forum will soon give you more specific information.
Thanking You

ToddAWalter 316 posts Joined 10/11
02 Apr 2014

Materialized view in Teradata are called Join Index and can be found in the manuals using that term.

prateek.saxena 12 posts Joined 08/13
04 Apr 2014

Hi Santanu,
Thanks for your reply. Yes,i need intermediate tables to use them further in my code before inserting the data in my final table.


You must sign in to leave a comment.