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 Data Warehouse SQL
Oracle Tips by Burleson

One of the most important enhancements to Oracle8 was improving the scalability of the Oracle database software. Starting with Oracle8, Oracle made a commitment to supporting very large databases and developed sophisticated partitioning schemes and enhancements directly targeted to data warehouse queries, and now Oracle databases can easily support data warehouses with terabytes of data. Along with this support of very large databases came changes to Oracle SQL to support very large queries.

For data warehouse SQL, we see several features that are germane to SQL and the tuning of SQL. These include:

  • Large table join tuning   This relates to the use of the star and ordered hints to improve the throughput of join operations against very large tables.

  • Oracle partitioning and SQL tuning   Oracle provides internal methods to allow specific partitions of tables and indexes to be accessed during execution of an SQL query. In most cases, however, this use of partitions is transparent to the SQL.

  • Oracle parallel query and SQL tuning   Oracle has enhanced Oracle parallel query to speed up large-table full-table scans and make aggregation operations faster.

  • Oracle optimization and data warehouse queries   The parse phase of data warehouse SQL can be very time-consuming, and Oracle provides several initialization parameters and techniques for limiting the amount of parsing performed for data warehouse queries.

In addition to the data warehouse features, we will also explore techniques that can be used to identify those SQL queries that might benefit from data warehouse features.

Let’s begin with a review of large tables and see how Oracle8i and Oracle handle very large tables with SQL.

Large Table Join Tuning

One of the foremost characteristics of data warehouse SQL queries is the presence of many tables in the SQL select statements. In standard star schema design, a central fact table is joined with numerous dimension tables.

As a matter of SQL tuning, we see the following SQL tuning techniques to improve the speed of data warehouse SQL statements:

  • The ordered hint   The ordered hint specifies the optimal way to join the tables together. This bypasses the expensive parse phase of data warehouse SQL and ensures that the tables are always joined in the same order. For details on tuning n-way joins, see Chapter 16.

  • The star hint   For queries of a fact table or a dimension table, the star hint can greatly improve the join speed of data warehouse queries. A permutation of the hash join, the star join techniques builds a hash index on the fact table indexes. For details on the star join, see Chapter 16.

  • Optimizer plan stability   The use of stored outlines also speeds up data warehouse queries and is a technique commonly used to bypass SQL join order parsing and ensure that SQL tuning changes are permanent. For details on stored outlines, see Chapter 13.

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