Volatile table vs views.

Database
Enthusiast

Volatile table vs views.

Hi,

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

3 REPLIES
Enthusiast

Re: Volatile table vs views.

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

Santanu

Teradata Employee

Re: Volatile table vs views.

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

Enthusiast

Re: Volatile table vs views.

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.

Regards,

Prateek