Tuesday, October 23, 2012

What are Global temporary tables and Volatile temporary 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 till the session persist, 
    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.

Volatile Temporary tables (VTT):

1. Table Definition is stored in System cache
2. Data is stored in spool space.
3. That's why, data and table definition both are active till session persist.
4. No collect stats for VTT. 
5. If you are using volatile table, you can not put the default values on column level while creating table.

No comments:

Post a Comment