What is the different between GLOBAL temporary table and LOCAL temporary table?

I know that the Local temporary table is session scoped. When is session end, the table will be drop automatic.But what about the GLOBAL temporary table? Seems it likes a real table, you need to drop it manually? What is the scenario if you are using the GLOBAL temporary table? Thanks, Martin


  • Options

    Kindly find the difference between Global Temporary Table & Local Temporary Table

    Global Temporary Tables
    HP Vertica creates global temporary tables in the public schema, with the data contents private to the transaction or session through which data is inserted.
    Global temporary table definitions are accessible to all users and sessions, so that two (or more) users can access the same global table concurrently. However, whenever a user commits or rolls back a transaction, or ends the session, HP Vertica removes the global temporary table data automatically, so users see only data specific to their own transactions or session.
    Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement.

    Local Temporary Tables
    Local temporary tables are created in the V_TEMP_SCHEMA namespace and inserted into the user's search path transparently. Each local temporary table is visible only to the user who creates it, and only for the duration of the session in which the table is created.
    When the session ends, HP Vertica automatically drops the table definition from the database catalogs. You cannot preserve non-empty, session-scoped temporary tables using the ON COMMIT PRESERVE ROWS statement.
    Creating local temporary tables is significantly faster than creating regular tables, so you should make use of them whenever possible.

    Rahul Choudhary
  • Options
    Hi Choudhary ,

    Thank you very much. It makes me more clear .

    Best regards,

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file