Database Reference
In-Depth Information
■
as of Oracle 12
c
, the following procedures of
DBMS_STATS
no longer issue a
COMMIT
as part of gathering
temporary table statistics for tables created with
ON COMMIT DELETE ROWS
:
GATHER_TABLE_STATS
,
DELETE_TABLE_STATS
,
DELETE_COLUMN_STATS
,
DELETE_INDEX_STATS
,
SET_TABLE_STATS
,
SET_COLUMN_STATS
,
SET_INDEX_STATS
,
GET_TABLE_
STATS
,
GET_COLUMN_STATS
,
GET_INDEX_STATS
. the prior procedures do issue an implicit
COMMIT
for temporary tables
defined as
ON COMMIT PRESERVE ROWS
.
Note
Direct-Path Load Automatic Statistics Gathering
Starting with Oracle 12
c
, when performing direct-path operations on a temporary table (where
ON COMMIT PRESERVE
ROWS
is enabled), session-level statistics are gathered by default for the temporary table being loaded. Two typical
direct-path load operations are
CREATE TABLE AS SELECT
(CTAS) and direct-path
INSERT
s (
INSERT
s with the
/*+
append */
hint).
A simple example will demonstrate this. Here we create a CTAS table:
EODA@ORA12CR1> create global temporary table gt on commit preserve rows
2 as select * from all_users;
Table created.
We can verify that session-level statistics have been generated via the following query:
EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
---------- ---------- -------------- -------
GT SHARED
GT 51 18-JAN-14 SESSION
This eliminates the need to call
DBMS_STATS
to generate statistics when direct-path loading a temporary table that
is defined as
ON COMMIT PRESERVE ROWS
.
Temporary Tables Wrap-up
Temporary tables can be useful in an application where you need to temporarily store a set of rows to be processed
against other tables, for either a session or a transaction. They are not meant to be used as a means to take a single
larger query and break it up into smaller result sets that would be combined back together (which seems to be the
most popular use of temporary tables in other databases). In fact, you will find in almost all cases that a single query
broken up into smaller temporary table queries performs more slowly in Oracle than the single query would have. I've
seen this behavior time and time again, when given the opportunity to rewrite the series of
INSERT
s into temporary
tables as
SELECT
s in the form of one large query, the resulting single query executes much faster than the original
multistep process.
Temporary tables generate a minimum amount of redo, but they still generate some redo. Prior to 12
c
there is no
way to disable that. The redo is generated for the rollback data, and in most typical uses it will be negligible. If you only
INSERT
and
SELECT
from temporary tables, the amount of redo generated will not be noticeable. Only if you
DELETE
or
UPDATE
a temporary table heavily will you see large amounts of redo generated.
