Global Temporary Table (GTT)

Global Temporary Table is also known as GTT. The global table is not much different than regular table you create with any database with few exceptions on data life. The table is global but not the data. What does that mean to you? The data on GTT is private meaning the session that inserts data can access it, no one else can access it. The table is accessible or say visible to anyone who has proper access to database and table.

The data on the table is short lived either for one transaction or for a session. This depends on the type of global table you define. Since the truncate is happening frequently, GTT requires low maintenance or no maintenance.

The ON Commit clause tells the database when to delete or preserver rows in a table. DELETE ROWS and PRESERVE ROWS are two options you can specify during global table creation.
When do you use these options?

DELETE ROWS: A transaction specific table is created meaning the data goes away at the end of a transaction. The transaction always ends with commit or rollback. The database truncates the GTT table after each commit.

PRESERVE ROWS: A session specific table is created. The data sticks even after commit or a transaction that inserted the data end. The data gets flushed out at the end of the session or whenever a TRUNCATE statement is issued. The database truncates the table only when you terminate the session.

Syntax: ON COMMIT DELETE ROWS

CREATE global TEMPORARY TABLE gtt_table_delete_on_commit( 
 dept_no NUMBER PRIMARY KEY,
 dept_name VARCHAR2(30),
 employee_count NUMBER)
ON COMMIT DELETE ROWS;

Global temporary TABLE created.

Display GTT table:

SELECT table_name,
 status,
 TEMPORARY
FROM dba_tables
WHERE table_name = gtt_table_delete_on_commit;

Output:

INSERT INTO gtt_table_delete_on_commit (
  dept_no,
  dept_name,
  employee_count)
 VALUES(
  10,
  'Rockstar DBA',
  15);

1 row inserted.

Select * from gtt_table_delete_on_commit;

Output:

commit;
select * from gtt_table_delete_on_commit;

no records:

The commit statement truncates the data from global temporary table. Next we are going to talk on on commit preserver rows.

Syntax: ON COMMIT PRESERVE ROWS

CREATE global TEMPORARY TABLE gtt_table_preserve_on_commit( 
 dept_no NUMBER PRIMARY KEY,
 dept_name VARCHAR2(30),
 employee_count NUMBER)
ON COMMIT PRESERVE ROWS;

Global temporary TABLE created.

INSERT INTO gtt_table_preserve_on_commit(
  dept_no,
  dept_name,
  employee_count)
 VALUES(
  10,
  'Rockstar DBA',
  15);

Commit;
select * From gtt_table_preserve_on_commit;

Output:

The data on PRESERVE ROW GTT are preserved even after commit. The data gets wiped out only after reconnecting the session.

Logout and re-connect and check GTT  data.

select * From gtt_table_preserve_on_commit;

no records

Logout or disconnecting from IDE terminates the session causing GTT table data to truncate.

How to check GTT ON COMMIT setting? Is it DELETE or PRESERVE on commit? How do you find out? I am not aware of  have any dictionary tables or views that store the commit setting but I use a DBMS package to spit out the DDL for any table, see below.

SELECT dbms_metadata.Get_ddl (object_type, object_name, owner) 
FROM   dba_objects 
WHERE  object_type = 'TABLE' 
       AND owner = 'BANIYA'  --table owner
       AND object_name = GTT_TABLE_PRESERVE_ON_COMMIT; --table name

Output:

CREATE GLOBAL TEMPORARY TABLE "BANIYA"."GTT_TABLE_PRESERVE_ON_COMMIT" 
   ( "DEPT_NO" NUMBER, 
 "DEPT_NAME" VARCHAR2(30), 
 "EMPLOYEE_COUNT" NUMBER, 
  PRIMARY KEY ("DEPT_NO") ENABLE
   ) ON COMMIT PRESERVE ROWS

After analyzing the DDL from dbms_metadata.get_ddl() package, we see the GTT is PRESERVE ROWS on commit.  If you are aware of views that stored the commit settings, please let me know or comment below.

INDEX: Can you have Index on GTT? You can have Indexes but again, they are temporary. The Index lives for a transaction or a session like data. This all depends on the commit clause we define on the table.

OTHERS: Like regular tables, you can specify database constraints like primary key, unique, not null, check, etc. on GTT tables. You may have restrictions on assigning regular tablespace, I would check Oracle document on tablespace.

A possible use case would be a user that has only rights to create reports. These reports use a lot of aggregated data and this information is reused in more than one of the reports. So it would be a good idea to cache the information for this session and this can be done by using temporary tables.

Why use global temporary table? GTT avoid using complex queries. It is an alternative to using WITH clause to materialize intermediate query result. GTT improves the speed of queries that perform complex summarization.

Temp Table Vs Regular Table:

1. GTT use temp tablespace where as regular table space is assigned to regular table.
2. GTT data is cleaned after a commit/transaction whereas regular table doesn’t clean itself.
3. GTT data last for a session or transaction whereas regular table last forever.
4. GTT data is accessible to a session or transaction that loaded data whereas regular table data are accessible to anyone who has select access.
5. GTT table requires less maintenance whereas regular table are high maintenance because they may get fragmented.
6. GTT don’t have stats whereas regular table has stats which is good. (might have changed for 12c) 

Prior to Version 12c, GTT undo segments are stored into regular undo tablespace. A database has to be in read-write more therefore GTT can’t be used in read-only database and physical standby databases. Oracle database 12c Release introduced the temporary undo segment for global tables to be stored in the temporary tablespace. This feature now allows GTT to be used on standby and read-only databases.