Using Global Temporary Tables - GTT

Purpose:

Global temporary tables are used when we have to prepare bulk data in a transaction/session.

The life cycle of the data will be cleared from temporary tables as soon the transaction committed or session closed.

We don't need a separate process to clear the data.

Generally we create purge scripts on permanent tables and schedule them for all practical purposes.

1. Keeping life cycle per transaction commit:

CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

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

SQL>

1. Keeping life cycle per session:

CREATE GLOBAL TEMPORARY TABLE my_tmp_tab (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_tmp_tab VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_tmp_tab;

  COUNT(*)
----------
         1

SQL>

-- Reconnect and check contents of GTT.
CONN soademo/soademo

SELECT COUNT(*) FROM my_tmp_tab;

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

SQL>


Features:
- Data in temporary tables automatically deleted at the end of the database session, even if it ends abnormally.
- Indexes can be created on the temporary tables. The life cycle of the index is same as database session.
- Views can be created on temporary tables and with combination of temporary and permanent tables.
- Truncate statement issued on temporary table will effect only that session.

Thanks to the blog for references.