GTT(Global Temporary Table)

Description:

The GTT abbreviates “Global Temporary Table”, but the table itself is not temporary, and the data within it is temporary.

The data stored in GTT(Global Temporary Table) is stored only as long as the session(session specific GTT) or transaction(Transaction specific GTT) lasts and is private for each session, however, the definition is visible to all sessions. After commit or disconnect the session, the data is lost but the definition of the table remains.
Likewise, other database objects related to the global temporary table like synonyms or views won’t disappear after the end of the transaction or session.
Indexes created on a temporary table behave similarly.
Temporary tables have all of the features that ordinary tables have like triggers or the statistics about table access cost, join cardinality, etc. as well as information about rows and blocks.
NOTE: Temporary tables can not have a foreign key related to other tables.

GTT operation perform in-memory(RAM) so reading data from GTT is a “Logical Read”, hence its faster.

Temporary Table Syntax:
The statement to create a GTT(Global Temporary Table) is similar to the definition of a simple table, with an addition to it there will be the keywords “GLOBAL TEMPORARY”.
In the clause “ON COMMIT”, you can specify “DELETE ROWS” if a table is transaction specific or else you can specify “PRESERVE ROWS” if the table is session specific.

GTT(Global Temporary Table) syntax:

transaction-specific(ON COMMIT DELETE ROWS) 

session-specific(ON COMMIT PRESERVE ROWS)

CREATE GLOBAL TEMPORARY TABLE table_name
( column_name column_data_type
  …
  …
ON COMMIT DELETE/PRESERVE ROWS;

Example:
SQL> CREATE GLOBAL TEMPORARY TABLE gtt_tab1 (
   EmpNo NUMBER(3),
   EName VARCHAR2(50)  NOT NULL) ON COMMIT DELETE ROWS;

Table created.

SQL> INSERT INTO gtt_tab1 VALUES (101, ‘SCOTT’);

1 row created.

SQL> INSERT INTO gtt_tab1 VALUES (102, ‘ALLEN’);

1 row created.

SQL> select * from gtt_tab1;

     EMPNO ENAME
———- ———————————–
       101 SCOTT
       102 ALLEN

SQL> commit;

Commit complete.

SQL> select * from gtt_tab1;

no rows selected

SQL> desc gtt_tab1
 Name                    Null?        Type
 ———————– ———— ————–
 EMPNO                                NUMBER(3)
 ENAME                   NOT NULL     VARCHAR2(50)

Reuse the Space Used by Temporary Tables:

Rows associated with created temporary tables are stored in the default temporary tablespace. Starting with Oracle 11g, Oracle introduced the option to specify the temp tablespace for a GTT. Within a TABLESPACE clause, you can allocate the temporary tablespace with a specified extent size.

CREATE TEMPORARY TABLESPACE tbl_spc1
TEMPFILE ‘tbl_spc_gtt1.f’ SIZE 50m REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE GLOBAL TEMPORARY TABLE gtt_tab1 (
   EmpNo NUMBER(3),
   EName VARCHAR2(50)  NOT NULL) ON COMMIT DELETE ROWS
TABLESPACE tbl_spc1;


New Feature “Private Temporary Tables” introduced in Oracle Database 18c.

With support to GTT(Global Temporary Tables) syntax one more new database object called “Private Temporary Tables” introduced in Oracle Database 18c.
The concept of a private temporary table, a memory-based temporary table which is going to drop at the end of the session or transaction depending on the setup.
In Oracle, a Global Temporary Table (GTT) is a permanent metadata object that holds rows in temporary segments on a transaction-specific or session-specific basis. It is not considered normal to create and drop GTTs on the fly. But, with the introduction of private temporary tables, Oracle implement a similar concept, where the table object itself is temporary, not just the data.

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn