Global Temporary Tables

Introduction

Applications often use some form of temporary data store for processes that are required only for that session / transaction. Post that transaction / Session that data is no more required and need not be stored in the Data Base. Oracle Provides Global Temporary Tables (GTT) that can be used for this purpose.

Global temporary tables are types of database tables which can privately store data a session or transaction. The data will be flushed automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

The Table data is session Specific, the table is available in all sessions like normal tables.

CREATE GLOBAL TEMPORARY TABLE <Table-name>
(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;



The Above syntax will be used to create the Global Temporary Tables.

Two types of GTT available based on the Options provided while creating the Temp table..

ON COMMIT DELETE ROWS

            Its transaction Specific temp table, the data in the table will get deleted when Commit/ Roll back statement is issued.

ON COMMIT PRESERVE ROWS

         It s session Specific temp table, the Data in the table will be available throughout the session until the data is deleted in the same session.  It both cases the data will get flushed when the session is closed normally / abnormally.

The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS

Example

Create Table with default option (on Commit Delete Rows)

SQL>  CREATE GLOBAL TEMPORARY TABLE TEMP_EXAMPLE

          (   NEW_NO     NUMBER

          ) ON COMMIT  DELETE ROWS;

Table created

 

SQL> INSERT INTO TEMP_EXAMPLE VALUES (10);

1 row inserted

SQL> INSERT INTO TEMP_EXAMPLE VALUES (11);

1 row inserted

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE;

  COUNT(1)

----------

         2

SQL> COMMIT;

Commit complete

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE;

  COUNT(1)

----------

         0

-- Create table with on Commit preserve Rows

SQL> CREATE GLOBAL TEMPORARY TABLE TEMP_EXAMPLE_1

              (   NEW_NO     NUMBER

              ) ON COMMIT  PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TEMP_EXAMPLE_1 VALUES (10);

1 row inserted

SQL> INSERT INTO TEMP_EXAMPLE_1 VALUES (15);

1 row inserted

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE_1;

  COUNT(1)

----------

         2

SQL> COMMIT;

Commit complete

SQL> SELECT COUNT(1) FROM TEMP_EXAMPLE_1;

  COUNT(1)

----------

         2

Features

Ø  Temporary tables cannot be created without “Global”   keyword.

Ø  GTT data is private to a session. Although there is a single table definition, each session uses a     GTT as if it was privately owned.

Ø  Truncating data in a temp table will not affect other users sessions.

Ø  Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.

Ø  Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.

Ø  In Oracle 11g , the temp tablespace can be used to create the temp tables.

Ø  Views can be created against temporary tables and combinations of temporary and permanent tables.

Ø  Foreign key constraints are not applicable for Temporary tables

Ø  Temporary tables can have triggers associated with them.

Ø  Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Ø  Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”

Ø  Temporary tables cannot be partitioned.

Ø  If GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.

Ø  If GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.