Hypercharge Oracle SQL with Temporary Tables
 

For certain types of SQL operations, the creation of intermediate result tables can result in stunning performance improvements. We will discuss how you can use the global temporary tables (GTT) 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.

Using temporary tables 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 noncorrelated subquery (against a complex view) as shown here:

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.

Please read these important notes on SQL tuning with temporary tables:

http://dba-oracle.com/t_temporary_tables_sql.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf

*****************************************

How do I do that in Oracle?

Explore this series for a collection of tips that explain in detail how to perform specific tasks in Oracle related to database administration. Consisting of expert advice from top Oracle gurus, these how-tos provide step-by-step instructions to guide you through database administration processes related to installation and upgrades, backup and recovery and other Oracle basics.

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1264060,00.html?Offer=ORburl0206