Global Temporary Table In Oracle

Introduction

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.

Note that only table data is session specific, but physically table is available in all sessions.

Syntax

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

The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS. An alternative is to specify ON COMMIT PRESERVE ROWS.

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
On the other hand, ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Simple Example

--Create a simple GTT with default options:

CREATE GLOBAL TEMPORARY TABLE
temp_gtt (id NUMBER(20)) ;

--Insert some data and do a SELECT

BEGIN
  FOR i in 1..10 LOOP
    INSERT INTO temp_gtt values (i) ;
  END LOOP ;
END ;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
10

--Do a commit and run the query again

COMMIT;

SELECT count(*) FROM temp_gtt;

--result:

COUNT(*)
----------------------
0

Important points about GTT

1. In Oracle temporary table and global temporary table are synonymous. You cannot create a temp table without the keyword “global”
2. 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 GTT will not affect other users sessions.
3. 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.
4. 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.
5. The table and any associated indexes are stored in the users temporary tablespace. Starting with 11g Oracle introduced the option to specify the temp tablespace for a GTT (but not its indexes).
6. Views can be created against temporary tables and combinations of temporary and permanent tables.
7. Foreign key constraints are not applicable in case of Temporary tables
8. Temporary tables can have triggers associated with them.
9. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
10. 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.”
11. Temporary tables cannot be partitioned.
12. If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
1. If your 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.
2. If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Advertisement