1. Non-interference between private sets of data.
2. Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do
the same thing).
3. Decreased redo generation as, by definition, they are non-logging.
However:
Mixing temporary tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses (badly).
Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.
Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.Note: All DDL includes two implicit commits so any rows in a GTT specified with ON COMMIT DELETE ROWS will empty the table.
When temporary tables can be useful
Better avoided isn't exactly the same as completely banished. There are some cases when using a temporary table is justifiable, but those cases are probably less frequent than commonly thought. As shown above temporary tables don't make life easy for the optimizer, which can lead to wrong choices on its part - and when the optimizer is wrong, it can be very wrong.
- Temporary tables are better used when
- They are filled only once in the session, or with data sets of comparable size and properties (distribution of data, etc.), in which case dynamic sampling can benefit statements.
- When the result set they contain is required by several queries that cannot be combined (updates of distinct tables)
- When the additional cost of populating them is dwarfed by the performance improvement of further operations compared to alternative solutions.
- The most significant advantage of temporary tables over other solutions is that they can be indexed. Although indexes add overhead to the process of filling a temporary table, it can be a strong argument in favour of using a temporary table when processes would benefit from an index that cannot be added to the original table(s) (either because of schema mismatches, for instance a join that must use a complex, non-deterministic result, or because data comes from a schema you only have select access to, or because data comes from an external table).
http://psoug.org/reference/gtt.html
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 trucated. There is no affect on the data of other sessions.
- Data in temporary tables is stored in temp segments in the temp tablespace.
- 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 the 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.
- Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
- There are a number of restrictions related to temporary tables but these are version specific.
For more information see:
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:
- See here for a global temporary table example.
For the full details of the huge benefits of temporary table query tuning. see my book "Oracle Tuning: The Definitive Reference".
Let’s begin by looking at how the creation of temporary tables can speed non-correlated subqueries 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 tablespace 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).
http://www.dba-oracle.com/t_temporary_tables_sql.htm
No comments:
Post a Comment