Tuning Data Warehouse
Oracle Tips by
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
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
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.