Temporary Tables
Temporary Tables
In addition to permanent tables, Oracle can
create temporary tables to hold session-private data that exists only
for the duration of a transaction or session.
The CREATE GLOBAL TEMPORARY TABLE statement
creates a temporary table which can be transaction-specific or
session-specific. For transaction-specific temporary tables, data exists for
the duration of the transaction. For session-specific temporary tables, data
exists for the duration of the session. Data in a temporary table is private to
the session. Each session can only see and modify its own data. DML locks are
not acquired on the data of the temporary tables. The LOCK statement has no
effect on a temporary table because each session has its own private data.
A TRUNCATE statement issued on a session-specific
temporary table truncates data in its own session. It does not truncate the
data of other sessions that are using the same table.
DML statements on temporary tables do not
generate redo logs for the data changes. However, undo logs for the data and
redo logs for the undo logs are generated. Data from the temporary table is
automatically dropped in the case of session termination, either when the user
logs off or when the session terminates abnormally such as during a session or
instance crash.
You can create indexes for temporary tables using
the CREATE INDEX statement. Indexes created on temporary tables are also
temporary, and the data in the index has the same session or transaction scope
as the data in the temporary table.
You can create views that access both temporary
and permanent tables. You can also create triggers on temporary tables.
The Export and Import utilities can export and
import the definition of a temporary table. However, no data rows are exported
even if you use the ROWS clause. Similarly, you can replicate the definition of
a temporary table but you cannot replicate its data.
Segment Allocation
Temporary tables use temporary segments. Unlike
permanent tables, temporary tables and their indexes do not automatically
allocate a segment when they are created. Instead, segments are allocated when
the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a
SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears
to be empty.
You can perform DDL statements (ALTER TABLE, DROP
TABLE, CREATE INDEX, and so on) on a temporary table only when no session is
currently bound to it. A session gets bound to a temporary table when an INSERT
is performed on it. The session gets unbound by a TRUNCATE, at session
termination, or by doing a COMMIT or ABORT for a transaction-specific temporary
table.
Temporary segments are deallocated at the end of the transaction
for transaction-specific temporary tables and at the end of the session for
session-specific temporary tables.
Parent and Child Transactions
Transaction-specific temporary tables are
accessible by user transactions and their child transactions. However, a given
transaction-specific temporary table cannot be used concurrently by two
transactions in the same session, although it can be used by transactions in
different sessions.
If a user transaction does an INSERT into the
temporary table, then none of its child transactions can use the temporary
table afterwards.
If a child transaction does an INSERT into the
temporary table, then at the end of the child transaction, the data associated
with the temporary table goes away. After that, either the user transaction or
any other child transaction can access the temporary table.
Applications often use some form of temporary data store for
processes that are to complicated to complete in a single pass. Often, these
temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i,
the maintenance and management of temporary tables can be delegated to the
server by using Global Temporary Tables.
Creation of Global Temporary Tables
The data in a global temporary table is private, such that data
inserted by a session can only be accessed by that session. The
session-specific rows in a global temporary table can be preserved for the
whole session, or just for the current transaction. The
ON COMMIT DELETE ROWS clause
indicates that the data should be deleted at the end of the transaction.CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
In contrast, the
ON COMMIT PRESERVE ROWS clause indicates that
rows should be preserved until the end of the session.CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
Miscellaneous Features
- If the TRUNCATE statement is
issued against a temporary table, only the session specific data is
trucated. There is no affect on the data of other sessions.
- Data in temporary tables is
automatically delete at the end of the database session, even if it ends
abnormally.
- Indexes can be created on
temporary tables. The content of the index and the scope of the index is
that same as the database session.
- Views can be created against
temporary tables and combinations of temporary and permanent tables.
- Temporary tables can have
triggers associated with them.
- Export and Import utilities can
be used to transfer the table definitions, but no data rows are processed.
- There are a number of
restrictions related to temporary tables but these are version specific.
0 comments:
Post a Comment