GLOBAL TEMPORARY TABLE

DROP TABLE G_MIKE_PO_LINES
/

CREATE GLOBAL TEMPORARY TABLE GLB_MIKE_PO_LINES
(
AMOUNT NUMBER,
TAX_CODE VARCHAR2(15 BYTE),
ACCOUNT VARCHAR2(150 BYTE),
PO_NUMBER VARCHAR2(20 BYTE),
PO_LINE_NUM NUMBER,
SHIPMENT_NUM NUMBER,
DISTRIBUTION_NUM NUMBER,
SKU_NUMBER VARCHAR2(150 BYTE),
ITEM_DESCRIPTION VARCHAR2(240 BYTE),
QUANTITY_INVOICED NUMBER,
SHIP_TO_LOCATION VARCHAR2(60 BYTE),
UNIT_PRICE NUMBER,
PROJECT VARCHAR2(30 BYTE),
TASK VARCHAR2(20 BYTE),
EXPENDITURE_ORG VARCHAR2(3 BYTE),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
EXPENDITURE_ITEM_DATE DATE
)
ON COMMIT DELETE ROWS;



Bookmark Go to End

Doc ID: Note:68098.1
Subject: Overview of Temporary Tables
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 19-JAN-1999
Last Revision Date: 17-AUG-2004



Introduction
~~~~~~~~~~~~
This is an overview of TEMPORARY TABLES introduced in Oracle8i. This
new feature allows temporary tables to be created automatically in a
users temporary tablespace.

Syntax
~~~~~~
CREATE GLOBAL TEMPORARY TABLE tablename ( columns )
[ ON COMMIT PRESERVE | DELETE ROWS ]

The default option is to delete rows on commit.

What Happens
~~~~~~~~~~~~
When you create a GLOBAL TEMPORARY table a dictionary definition of
the table is created. As soon as the table gets populated (on the first
INSERT or at creation time for CTAS operations) a temporary segment is
created in the users default TEMPORARY tablespace location. This temporary
segments contents are just like a normal table.

Different sessions using the same GLOBAL TEMPORARY table get allocated
different temporary segments. The temporary segments are cleaned up
automatically at session end or transaction end depending on the specified
duration (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).

Apart from the data visibility temporary tables can be used like ordinary
tables for most operations.

@Internally TAB$ has FILE#/BLOCK#/TS# all set to 0 for temporary tables.

Characteristics
~~~~~~~~~~~~~~~

1. Data exists only for the duration of either the session or
transaction.

This can be specified in the create table command.
For example:

SQL> Create global temporary table emp_temp(eno number)
on commit delete rows;

- OR -

SQL> Create global temporary table emp_temp(eno number)
on commit preserve rows;


ON COMMIT DELETE ROWS indicates a transaction level duration and
PRESERVE indicates a session level duration.

2. Data is visible only at session or transaction level. Multiple
users using the same temporary table can see the definition
of the table and their own data segment and nothing else.

3. Indexes, triggers and views can be created on these tables.

4. If an Index is created on temporary tables then it MUST be created
when the table is empty - ie: When there are NO temporary segments for
incarnations of the table. Indexes are implemented as separate
temporary segments.

5. No redo is generated for operations on the temporary table itself BUT
undo is generated. Redo *IS* generated for the undo so temporary tables
do indirectly generate redo.

6. The keyword GLOBAL indicates the table definition can be viewed
by anybody with sufficient privileges - ie:using the same rules
that apply to normal user tables. Currently only GLOBAL TEMPORARY
tables are supported.

7. TRUNCATE operations truncate only the current session's incarnation
of the table.

8. You can only export or import the definition not the data.

9. Segments get created only on the first insert (or CTAS) operation.


Drawbacks
~~~~~~~~~

1. The table definition is not dropped automatically.

2. Only GLOBAL tables are supported right now, not local ones.

3. Can perform DDL only when no session is bound to it.

4. There is no underlying support for STATISTICS on GLOBAL
TEMPORARY tables so CBO (Cost Based Optimizer) has no statistical
information to help determine an execution plan.
NB: "ANALYZE TABLE COMPUTE/ESTIMATE STATISTICS" returns success
even though no statistics are gathered in 8i. 9i and higher
provides support for STATISTICS.


Constraints
~~~~~~~~~~~

Constraints can be implemented on the table either at the session or
transaction level depending on the scope of the temporary table and
are not for the entire table even though the constraint is defined
for the entire table.

If there is a primary key or unique key constraint, it is applicable only at
either session or transaction leve i.e. two users can enter the same values
into the table from different sessions even though you have a primary / unique
key in place for the entire table (if the scope is the session )

In the case of a transaction level temporary table, the same values
can be entered from different transactions.