Oracle Partitioning and Oracle SQL Tuning
Oracle Tips by Burleson Consulting
In order to improve the manageability of
large tables and indexes in Oracle8, Oracle introduced the concept
of object partitioning. Once a table or index is defined as
partitioned , Oracle SQL automatically detects the partition
segments and determines the appropriate segment to access during a
WARNING – You should always ensure that you are
using the cost-based optimizer when using partitions because the
rule-based optimizer will not use the performance enhancements of
partition-wise joins and exclusive partition access. Make sure
that your optimizer_mode is set to first_rows or all_rows.
Partitioning and SQL table joining
The use of partitioned tables and indexes
will also improve the speed of table join operations in a specials
case of a join called a partition-wise join.
In a partition-wise join, Oracle divides the
join into smaller joins that occur between each of the partitions on
which the tables reside, completing the overall join in less time.
There are two types of partition-wise joins.
The first variation requires that both table reside in partitions,
but Oracle also supports a partial partition-wise join, whereby only
one of the table is partitioned. Of course, the access of the
partitions is transparent to the SQL, and the only way to see the
partition access is to view the execution plan for the SQL.
In cases of partitioned full-table scans,
parallelism can also be combined with partitioning. When parallelism
is combined such that each partition has a parallel query process,
the dividing of the table becomes faster an the overall execution
time is faster.
Partitioning and SQL execution speed
When a partitioned index is analyzed with the
analyze command, the Oracle data dictionary is populated with
information about the ranges of values within each partition. This
information is used by Oracle to include only those partitions that
are relevant to the SQL query.
For example, suppose you have defined a
partitioned index and an SQL statement requests an index range scan
within a single index partition. Oracle will only access that index
partition and can invoke an index fast-full scan against the
partition, thereby improving the query speed because the entire
index did not need to be accessed. Oracle calls this feature
partition-aware optimization. At the heart of partition-aware
optimization is the requirement that Oracle only access those
partitions that are required to service the SQL query.
If you know the partition that contains your
data you can explicitly reference it in your SQL query. While the
CBO should be intelligent enough to recognize that a single
partition is required, the partition clause make it very clear which
partition is being referenced.
fact PARTITION ( march_2001_sales )
sales_month = ‘2001-03’
Next, let’s examine how Oracle parallel query
can be used with data warehouse SQL.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.