Global Temporary tables

During the application development, we use some staging table or temporary tables to store intermediate result which are complicated to complete in one pass. Normally we use regular table or PLSQL table(array) to capture the intermediate result. But oracle8i introduced global temporary table. We can use global temporary table instead of regular table.

GLOBAL TEMPORARY TABLE indicates that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table

Please remember, the below codes are tested in oracle10g R2.

What are the advantages of Global temporary table compared to regular table?

Let us call Global temporary table as GTT....

1. GTT does not generate REDO info by itself. But regular table does generate redo infomation. So it will be faster when we use GTT.

2. When we truncate the GTT, it truncates only session specific data. So others can still see their data. But regular table truncate will truncate the whole table.

3. Data in GTT is deleted when the session ends or commit/rollback the transaction. So GTT data is session specific. But regular table is not session specific. It is visible of all the session once the data are committed.

4. Index can be created on GTT. But scope of the index data is at the session level.

5. Views can be created against GTT and combination GTT and regular table

6. Like regular table, database triggers can be created on GTT

Restriction on Global temporary tables :

1. GTT can not be partitioned.

2. We can not create any Foreign key constraint on GTT

3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored

4. GTT cannot contain columns of nested table.

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

Creation of Global temporary tables

The session-specific rows in a global temporary table can be preserved for the whole session. The data will be deleted when we exit the session. The ON COMMIT PRESERVE ROWS clause indicates that the data should be deleted at the end of the session.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT PRESERVE ROWS;

Table created.

scott@orcl> insert into gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

COLUMN1 COLUMN2
---------- ----------
1 1

scott@orcl> connect scott/tiger@orcl
Connected.
scott@orcl> select * from gtt;

no rows selected

scott@orcl>

The transaction-specific rows in a global temporary table can be preserved until we commit or rollback the transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted when we commit or rollback the transaction.

scott@orcl> CREATE GLOBAL TEMPORARY TABLE gtt(
2 column1 NUMBER,
3 column2 NUMBER
4 ) ON COMMIT DELETE ROWS;

Table created.

scott@orcl> INSERT INTO gtt values(1,1);

1 row created.

scott@orcl> commit;

Commit complete.

scott@orcl> select * from gtt;

no rows selected

scott@orcl>