|
|
|
Oracle:
SQL Tuning with temporary tables
One of the
shortcomings of relational databases is
their inability to create intermediate
result sets. One technique that is used
within all relational databases is the
creation of interim (temporary) tables to
improve the speed of their SQL queries.
The Oracle
database codified this approach starting and
Oracle8i with their global temporary tables
construct and we will be doing an exercise
later in this tutorial so that you can
understand firsthand how you can replace
complex subqueries with temporary tables in
order to improve the speed and readability
of certain queries.
Temporary
tables are generally used as a mechanism to
pre-join tables, or pre-aggregate
intermediate data. As such, the use of
temporary tables is more effective in highly
normalized databases. A data warehouse with
lot’s of denormalization may not benefit
from queries that use temporary tables.
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 as shown here:
select
username
from
dba_users
where
username NOT IN
(select grantee from
dba_role_privs);
On a large
database, this query runs in about 18
seconds. 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-fold
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.
Temporary
tables are also useful in cases where we
need to compare two ranges of dates in a
single table. For example, consider the
following STATSPACK query.
select distinct
to_char(old_size.snap_time,'yyyy-mm-dd'),
-- The old snapshot date
sum(old_size.bytes),
sum(new_size.bytes),
sum(new_size.bytes) -
sum(old_size.bytes)
from
stats$tab_stats old_size,
stats$tab_stats new_size
where
-- This is the highest date in the
table
new_size.snap_time = (select
max(snap_time) from stats$tab_stats)
and
-- This is the prior weeks snapshot
old_size.snap_time = (select
min(snap_time)-7 from stats$tab_stats)
group by
to_char(old_size.snap_time,'yyyy-mm-dd')
;
Here is the
execution plan for this query. Because we
are summing and comparing ranges of values
within the same table, we see the dreaded
MERGE JOIN CARTESIAN access method. As you
know, a Cartesian merge join can run for
hours because the Cartesian products of the
tables must be derived.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME POSITION
------------------------------
---------------------------- ----------
SELECT STATEMENT
5
SORT
GROUP
BY
1
MERGE JOIN
CARTESIAN
1
TABLE ACCESS
BY INDEX ROWID
STATS$TAB_STATS 1
INDEX
RANGE SCAN
TAB_STAT_DATE_IDX 1
SORT
AGGREGATE
1
INDEX
FULL SCAN (MIN/MAX)
TAB_STAT_DATE_IDX 1
SORT
JOIN
2
TABLE ACCESS
BY INDEX ROWID
STATS$TAB_STATS 1
INDEX
RANGE SCAN
TAB_STAT_DATE_IDX 1
SORT
AGGREGATE
1
INDEX
FULL SCAN (MIN/MAX)
TAB_STAT_DATE_IDX 1
By the way,
this is a very important Oracle query
because it can be used to show the database
size change. Here is an example of the
output:
Database
size change
comparing the most
recent snapshot dates
DB_NAME OLD_BYTES NEW_BYTES
CHANGE
--------- ------------- ----------------
----------------
prodzz1 467,419,136
572,424,192 105,005,056
------------- ----------------
----------------
Total 467,419,136
572,424,192 105,005,056
However,
this report is not terribly useful if the
SQL takes 20 hours to complete! The savvy
database professional will use temporary
tables to extract the summaries of the data
ranges and make our SQL run up to 30 times
faster.
In sum, the
use of temporary tables to extract
intermediate row sets can make a dramatic
difference in SQL execution times.
|
|
|
|
|
|
