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



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:

   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

create table
  select distinct

   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