Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips
 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security
 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote support
 Remote plans
 Remote services
 Application Server
 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support
 Remote Support  
 Development  
 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


  Oracle Posters
 Oracle Books
  Oracle Scripts
 Ion
 Excel-DB   


 

 

 


 

 
 

 

 

Oracle Global Temporary Tables


Oracle Tips by Burleson Consulting
 

Global; temporary tables are an alternative to using the WITH clause to materialize intermediate query results.

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


In addition to data dictionary queries, global temporary tables can dramatically improve the performance of certain SQL self-join queries that summarize data values.

The Elapsed-Time Section of the Date Range Report is a very sophisticated DBA report, and one that can run for many hours without the use of temporary tables because of Oracle’s use of the CARTESIAN access method. However, with the use of temporary tables, the table and index counts can be summarized and saved in the temp tables for fast analysis. We also use the same technique to sum the number of bytes in all tables and indexes into temporary tables, and then quickly interrogate the summary tables for total sizes of our database.

You can use the global temporary tables (GTT) syntax to improve the speed of queries that perform complex summarization activities.

Upon close examination, we see that we create temporary tables to hold the total counts, and we also create two temporary tables to hold the sum of bytes for each table and index. Once the sums are pre-calculated, it becomes fast and easy for Oracle SQL to compute the total bytes for the whole database.

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.

In any Oracle database, DDL statements should be used for maintenance tasks, not during normal system operations. For example, use global temporary tables or PL/SQL tables rather than permanent tables for reports. If this guideline is followed, in most systems, the frequency of DDL statements should be low.

 

 
  
 

 
 
 
 
Oracle performance tuning software
 
 

 

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

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2012 

All rights reserved.

Oracle © is the registered trademark of Oracle Corporation.