Global Temporary Tables
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 truncated. There is no affect on the data of other
sessions.
- Data
in temporary tables is stored in temp segments in the temp table space.
- Data
in temporary tables is automatically deleted 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.
|
Speed Oracle SQL with Temporary Tables
|
For certain types of
SQL operations, the creation of intermediate result tables can result in
stunning performance improvements. We will discuss how you can use the global
temporary tables (GTT) syntax to improve the speed of queries that perform
complex summarization activities, and how to speed up two-stage queries that
perform both summarization and comparison activities.
Please read these
important notes on SQL tuning with temporary tables:
Let’s begin by looking
at how the creation of temporary tables can speed non-correlated sub queries
against the Oracle data dictionary.
Using temporary tables
with Dictionary Views
The prudent use of
temporary tables can dramatically improve Oracle SQL performance. To illustrate
the concept, consider the following example from the DBA world. In the query
that follows, we want to identify all users who exist within Oracle who have
not been granted a role. We could formulate the query as an anti-join with a
noncorrelated subquery (against a complex view) as shown here:
SELECT username
FROM dba_users
WHERE username NOT IN (SELECT grantee FROM dba_role_privs);
This query runs in 18
seconds. As you may remember from Chapter 12, these anti-joins can often be
replaced with an outer join. However, we have another option by using CTAS.
Now, we rewrite the same query to utilize temporary tables by selecting the
distinct values from each table.
CREATE
TABLE temp1
AS
SELECT username FROM dba_users;
CREATE
TABLE temp2
AS
SELECT DISTINCT
grantee FROM dba_role_privs;
SELECT
username
FROM temp1
WHERE username NOT IN
(SELECT grantee FROM temp2);
With the addition of
temporary tables to hold the intermediate results, this query runs in less than
three seconds, a 6× performance increase. Again, it is not easy to quantify the
reason for this speed increase, since the DBA views do not map directly to
Oracle tables, but it is clear that temporary table show promise for improving
the execution speed of certain types of Oracle SQL queries.
Using Temporary Tables
If the amount of data
to be processed or utilized from your PL/SQL procedure is too large to fit
comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a
normal table. A GLOBAL TEMPORARY table has a persistent definition but data is
not persistent and the global temporary table generates no redo or rollback
information. For example if you are processing a large number of rows, the
results of which are not needed when the current session has ended, you should
create the table as a temporary table instead:
create
global temporary table
results_temp (...)
on
commit preserve rows;
The “on commit
preserve rows” clause tells the SQL engine that when a transaction is committed
the table should not be cleared.
The global temporary
table will be created in the users temporary table space when the procedure
populates it with data and the DIRECT_IO_COUNT will be used to govern the IO
throughput (this usually defaults to 64 blocks).
