Temporary Tables Wrap-Up-Database Tables
By Michele leonti / April 24, 2023 / No Comments / An Overview of Oracle Indexes, Index-Organized Tables Wrap-Up, Oracle Certifications, Reverse Key Indexes
Oracle provides two types of temporary tables: global and private. Either type of temporary table 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 resultsets 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 INSERTs into temporary tables as SELECTs 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 12c, 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.
Note You can instruct Oracle to write undo to the temporary tablespace and thereby eliminate almost all of the redo generation. This is done by setting the TEMP_UNDO_ENABLED parameter to TRUE (see Chapter 9 for details).
Statistics used by the CBO can be generated on a global temporary table with care; however, a better guess set of statistics may be set on a temporary table using the DBMS_STATS package or dynamically collected by the optimizer at hard parse time using dynamic sampling.
Starting with Oracle 12c, you can generate statistics specific to a session (for global temporary tables). This provides the optimizer with better information to generate execution plans that are more optimal for the data loaded in a given session.