 |
|
Tuning SQL with
Temporary Tables
Oracle Tips by
Burleson
|
For certain types of SQL operations, the
creation of intermediate result tables can result in stunning
performance improvements. This chapter will discuss how you can use
the create table as select (CTAS) 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. This chapter contains the following topics:
Let’s begin by looking at how the creation of
temporary tables can speed non-correlated subqueries against the
Oracle data dictionary.
Using CTAS 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 non-correlated subquery 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.
drop table
temp1;
drop table temp2;
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.
Next, let’s look at tuning aggregate queries
with temporary tables. These types of queries that summarize and
compare ranges of values within temporary tables will run far faster
if we create intermediate tables for the query.
TIP: If you are using Oracle8i and
above, you can use global temporary tables instead of dropping and
creating your own temporary tables.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
