|
 |
|
Speed Oracle SQL with Temporary Tables
Don Burleson |
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:
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).
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
