Temporary Tables

Temporary tables hold intermediate results that exist only for a short time, usually for the life of a session or a transaction. They keep scratch data out of permanent tables.

Declared vs created temporary tables

• Declared global temporary table (DGTT) :- Created with DECLARE GLOBAL TEMPORARY TABLE inside a session. Only that session can see it, and it disappears when the session ends.
• Created global temporary table (CGTT) :- Created once with CREATE GLOBAL TEMPORARY TABLE and stored in the catalog. Every session gets its own private copy of the data.
• Use DGTT for quick scratch work inside one program run. Use CGTT when many programs share the same scratch table layout.
• Both types avoid logging overhead of permanent tables and never need RUNSTATS for the optimizer.

Example: declared temporary table

• Example that stages high-salary employees for further processing:-
  DECLARE GLOBAL TEMPORARY TABLE SESSION.HIGH_SAL (EMPNO CHAR(6) NOT NULL, NAME VARCHAR(30), SALARY DECIMAL(9,2)) ON COMMIT PRESERVE ROWS; INSERT INTO SESSION.HIGH_SAL SELECT EMPNO, FIRSTNME, SALARY FROM EMPLOYEE WHERE SALARY > 60000;
• Always qualify the table with the SESSION schema name.
• ON COMMIT PRESERVE ROWS keeps the rows after a commit; the default ON COMMIT DELETE ROWS empties the table at commit.

Example: created temporary table

• Example that defines a shared scratch layout once in the catalog:-
  CREATE GLOBAL TEMPORARY TABLE DEPT_TOTALS (DEPTNO CHAR(3) NOT NULL, TOTAL DECIMAL(11,2)) ON COMMIT DELETE ROWS;
• Each session sees only its own rows, so concurrent programs never mix data.
• Indexes can be created on CGTTs; DGTTs support indexes only after the DECLARE in the same session.