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.
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.
Thanks for your reply. Yes,i need intermediate tables to use them further in my code before inserting the data in my final table.