Oracle Global Temporary Tables

Oracle Global Temporary Tables Version 11.2.0.3

General Information

Data Dictionary Objects

ALL_ALL_TABLES	DBA_ALL_TABLES	USER_ALL_TABLES
ALL_COL_COMMENTS	DBA_COL_COMMENTS	USER_COL_COMMENTS
ALL_TABLES	DBA_TABLES	USER_TABLES
ALL_TAB_COLS	DBA_TAB_COLS	USER_TAB_COLS
ALL_TAB_COMMENTS	DBA_TAB_COMMENTS	UESR_TAB_COMMENTS
ALL_TAB_PRIVS	DBA_TAB_PRIVS	USER_TAB_PRIVS
COL$	TAB$

System Privileges

ALTER ANY TABLE	DELETE ANY TABLE	LOCK ANY TABLE
CREATE ANY TABLE	DROP ANY TABLE	SELECT ANY TABLE
COMMENT ANY TABLE	FLASHBACK ANY TABLE	UNDER ANY TABLE
CREATE TABLE	INSERT ANY TABLE	UPDATE ANY TABLE

Create Global Temporary Table

Global temporary tables have three major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing).

3. Decreased redo generation as, by definition, they are non-logging.

However:

Mixing temporary tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses (badly).

Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.

Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.

Note:All DDL includes two implicit commits so any rows in a GTT specified with ON COMMIT DELETE ROWS will empty the table.

Create Global Temporary Table That Empties On Commit

You can not specify a tablespace with global temporary tables. GTT's are built in the TEMP tablespace.

CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>);

or explicitly specifying the ON COMMIT action

CREATE GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_data_type>,
<column_name> <column_data_type>,
<column_name> <column_data_type>)
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code   VARCHAR2(5),
by_user    VARCHAR2(30),
entry_date DATE);

-- or explicitly specifying the ON COMMIT action

CREATE GLOBAL TEMPORARY TABLE gtt_zip2 (
zip_code     VARCHAR2(5),
by_user      VARCHAR2(30),
entry_date   DATE)
ON COMMIT DELETE ROWS;

set linesize 121

SELECT table_name, tablespace_name, temporary, duration
FROM user_tables;

INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

SELECT *
FROM gtt_zip1;

COMMIT;

SELECT *
FROM gtt_zip1;

-- statistics collection demo: obviously there is a problem
INSERT INTO gtt_zip1
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

exec dbms_stats.gather_table_stats(USER, 'GTT_ZIP1');

SELECT *
FROM gtt_zip1;

Create Global Temporary Table That Empties At End Of Session

CREATE GLOBAL TEMPORARY TABLE gtt_zip3 (
zip_code     VARCHAR2(5),
by_user      VARCHAR2(30),
entry_date   DATE)
ON COMMIT PRESERVE ROWS;

set linesize 121

SELECT table_name, tablespace_name, temporary, duration
FROM user_tables;

INSERT INTO gtt_zip3
(zip_code, by_user, entry_date)
VALUES
('98000', USER, SYSDATE);

SELECT *
FROM gtt_zip3;

COMMIT;

SELECT *
FROM gtt_zip3;

-- log on as a different user
-- log back on as original user

SELECT *
FROM gtt_zip3;

GTT Demo

Redo Generation by GTT Type

CREATE TABLE reg_tab (
testcol VARCHAR2(100));

CREATE GLOBAL TEMPORARY TABLE gtt_ocd (
testcol VARCHAR2(100))
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_ocp (
testcol VARCHAR2(100))
ON COMMIT PRESERVE ROWS;

col value format 999999999999

-- get baseline redo value
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a heap table
BEGIN
FOR i IN 1 .. 1000 LOOP
    INSERT INTO reg_tab
    (testcol)
    VALUES
    (RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT DELETE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
    INSERT INTO gtt_ocd
    (testcol)
    VALUES
    (RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT PRESERVE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
    INSERT INTO gtt_ocp
    (testcol)
    VALUES
    (RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- results

Description	Value	Redo Generated
Baseline	254269080	-
Regular Table Run	254605916	336836
On Commit Delete	254742528	136612
On Commit Preserve	254879140	136612

Commenting

Comment a table

COMMENT ON TABLE <table_name> IS '<comment>';

COMMENT ON TABLE gtt_zip1 IS 'US Postal Service Zip Codes';

SELECT table_name, comments
FROM user_tab_comments
WHERE comments IS NOT NULL;

Comment a column

COMMENT ON COLUMN <table_name.column_name> IS '<comment>';

COMMENT ON COLUMN gtt_zip1.zip_code IS '5 Digit Zip Code';

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments IS NOT NULL;

Alter Table Column Clauses

Add a new column

ALTER TABLE <table_name> MODIFY (<field_name data_type>);

desc gtt_zip1

ALTER TABLE gtt_zip1 ADD (map_id NUMBER(10));

desc gtt_zip1

Add More Than One New Column

ALTER TABLE <table_name> MODIFY (<field_name data_type>, <field_name data type>);

ALTER TABLE gtt_zip1 ADD (map_coor VARCHAR2(10), map_ver VARCHAR2(3));

desc gtt_zip1

Rename A Column

ALTER TABLE <table_name> RENAME COLUMN <current_name> TO <new_name>;

ALTER TABLE gtt_zip1 RENAME COLUMN map_coor TO map_coord;

desc gtt_zip1

Drop A Column

ALTER TABLE <table_name> DROP COLUMN <column_name>;

ALTER TABLE gtt_zip1 DROP COLUMN map_coord;

desc gtt_zip1

Alter Table Change Data Type

ALTER TABLE <table_name> MODIFY (<column_name new_data_type);

desc gtt_zip1

ALTER TABLE gtt_zip1 MODIFY (zip_code VARCHAR2(6));

desc gtt_zip1

Alter Table Change Data Type Multiple Fields

ALTER TABLE <table_name>
MODIFY (<column_name> <data_type>, <column_name> <data_type>, ...);

desc gtt_zip1

ALTER TABLE gtt_zip1
MODIFY (zip_code VARCHAR2(7), entry_Date TIMESTAMP WITH TIME ZONE);

desc gtt_zip1

Drop Table

Drop Table Into Recycle Bin

DROP TABLE <table_name>;

DROP TABLE gtt_zip1;

Drop Table Completely Bypassing the Recycle Bin

DROP TABLE <table_name> PURGE;

DROP TABLE gtt_zip3 PURGE;

Foreign Key Constraints

GTT and Foreign Key

CREATE TABLE ptemp (
pid      NUMBER(5),
zip_code VARCHAR2(5));

ALTER TABLE ptemp
ADD CONSTRAINT pk_ptemp_pid
PRIMARY KEY (pid);

CREATE GLOBAL TEMPORARY TABLE gtt_zip1 (
zip_code   VARCHAR2(5),
by_user     VARCHAR2(30),
entry_date DATE);

ALTER TABLE gtt_zip1
ADD CONSTRAINT pk_gtt_zip1
PRIMARY KEY (zip_code);

ALTER TABLE ptemp
ADD CONSTRAINT fk_ptemp_gtt
FOREIGN KEY (zip_code)
REFERENCING gtt_zip1 (zip_code);

ALTER TABLE gtt_zip1
ADD CONSTRAINT fk_gtt_ptemp
FOREIGN KEY (zip_code)
REFERENCING ptemp (zip_code);

Indexes

Indexing GTT Columns

CREATE GLOBAL TEMPORARY TABLE gtt_zip4 (
zip_code VARCHAR2(5),
by_user VARCHAR2(30),
entry_date DATE);

desc gtt_zip4

CREATE INDEX ix_gtt_zip4_user
ON gtt_zip1(by_user);

SELECT table_name, index_name, tablespace_name
FROM user_indexes;

-- GTT indexes are stored in the TEMP tablespace