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 SQL query.

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.

Select /*+ full */
   sum(sales)
from
   fact PARTITION ( march_2001_sales )
where
   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.