Database Reference
In-Depth Information
•
They cannot be in a cluster of any type.
•
They cannot be partitioned.
ANALYZE
table command.
One of the drawbacks of a temporary table in any database is the fact that the optimizer has no real statistics on
it normally. When using the
cost
-
based optimizer
(
CBO
), valid statistics are vital to the optimizer's success (or failure).
In the absence of statistics, the optimizer will make guesses as to the distribution of data, the amount of data, and the
selectivity of an index. When these guesses are wrong, the query plans generated for queries that make heavy use of
temporary tables could be less than optimal. In many cases, the correct solution is to not use a temporary table at all,
but rather to use an
INLINE VIEW
(for an example of an
INLINE VIEW
, refer to the last
SELECT
just run—it has two of
them) in its place. In this fashion, Oracle will have access to all of the relevant statistics for a table and can come up
with an optimal plan.
I find many times people use temporary tables because they learned in other databases that joining too many
tables in a single query is a bad thing. This is a practice that must be unlearned for Oracle development. Rather than
trying to outsmart the optimizer and breaking what should be a single query into three or four queries that store their
subresults into temporary tables and then combining the temporary tables, you should just code a single query that
answers the original question. Referencing many tables in a single query is OK; the temporary table crutch is not
needed in Oracle for this purpose.
In other cases, however, the use of a temporary table in a process is the correct approach. For example, I once
wrote a Palm sync application to synchronize the date book on a Palm Pilot with calendar information stored in
Oracle. The Palm gives me a list of all records that have been modified since the last hot synchronization. I must take
these records and compare them against the live data in the database, update the database records, and then generate
a list of changes to be applied to the Palm. This is a perfect example of when a temporary table is very useful. I used
a temporary table to store the changes from the Palm in the database. I then ran a stored procedure that bumps the
Palm-generated changes against the live (and very large) permanent tables to discover what changes need to be
made to the Oracle data, and then to find the changes that need to come from Oracle back down to the Palm. I have
to make a couple of passes on this data. First, I find all records that were modified only on the Palm and make the
corresponding changes in Oracle. I next find all records that were modified on both the Palm and my database since
the last synchronization and rectify them. Then I find all records that were modified only on the database and place
their changes into the temporary table. Lastly, the Palm sync application pulls the changes from the temporary table
and applies them to the Palm device itself. Upon disconnection, the temporary data goes away.
The issue I encountered, however, is that because the permanent tables were analyzed, the CBO was being used.
The temporary table had no statistics on it (you can analyze the temporary table but no statistics are gathered), and
the CBO would guess many things about it. I, as the developer, knew the average number of rows I might expect, the
distribution of the data, the selectivity of the indexes, and so on. I needed a way to inform the optimizer of these
better
guesses. This is done through generating statistics for a temporary table. That brings us to the next topic regarding
how statistics are generated for a temporary table.
•
They cannot have statistics generated via the
■
since there are significant enhancements to gathering the temporary table statistics introduced in Oracle 12
c
,
I'm going to split the topic of gathering temporary table statistics into two sections: “statistics prior to 12c” and
“statistics starting with 12c.”
Note
Statistics Prior to 12c
There are three ways to give the optimizer statistics on the global temporary tables. One is via dynamic sampling
(starting with in Oracle9
i
Release 2 and above) and the other is the
DBMS_STATS
package, which has two ways to
accomplish this. First, let's look at dynamic sampling.
