 |
|
Oracle Concepts by Burleson
Consulting |
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 Oracle 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 Oracle 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 Oracle database size change.
Here is an example of the output:
Oracle 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 Oracle 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.
For more
details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
