Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.