Saturday, November 3, 2012

Few differences between Global Temporary Table and Volatile Table

 Global Temporary tables (GTT) :

  • Table Definition is stored  into Data Dictionary.
  • Data is stored in temp space.
  • GTT data is active upto the session ends, but table definition will remain there in Data dictionaly untill is is dropped  using Droptable statement.
  • secondry Index can be created on Global Temporary table.
  • Stats can be collected on GTT.
  • CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are supported by Global Temporary table.
  • In a single session 2000 Global temporary table can be materialized.


Volatile Temporary tables (VTT) :

  • Table Definition is stored in System cache.
  • Data is stored in spool space.
  • VTT data and table definition both are active only upto session ends.
  • secondry Index can not be created on Global Temporary table.
  • stats cannot be collected on VTT.
  • CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are not supported by GTT.
  • In a single session 1000 Volatile temporary table can be materialized.
  • VTT does not support default value for a column while creating a table.

1 comment:

  1. in td 12 (or 13?) onwards stat collection on VT's are already allowed. Thanks for the rest of the info though.

    ReplyDelete