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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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:

  • Using CTAS with dictionary views

  • Tuning aggregation queries with temporary tables

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

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

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter