Partitioning Tables  «Prev  Next»
Lesson 8Partition-wise Queries and Joins
ObjectiveDescribe how to create a partition-wise query or join.

Partition-wise Queries and Joins

Oracle's Optimizer is smarter than ever. A new feature of the Optimizer takes advantage of partitioned tables. Called the partition-wise join, this new feature uses the partitions to divide the query up into multiple queries that can be executed in parallel [1]. This can, in some cases, result in performance improvements. It is generally not recommended for online transactions, but should be considered for overnight batch jobs that crunch millions of rows at a time.
The complete details of setting up your environment and your partitioned tables so that the Optimizer can use partition-wise joins are more complex than we can cover here.
However, we have outlined the basic steps to provide you an overview of the process.
The following SlideShow illustrates the steps to preparing and creating a partition-wise join that uses , especially tables that are equi-partitioned[2].

Enabling Partition Wise Join
Even after you have enabled partition-wise joins, the Optimizer may not use this technique if it determines that a different execution path is more efficient. The next lesson wraps up this module.

Partitioning Concepts - Quiz

Click the Quiz link below to test your knowledge of this lesson and previous lessons.
Partitioning Concepts - Quiz

[1]Parallel queries: Queries are divided into smaller sections, and each section of the query executes simultaneously (in parallel) in the database server. This is not the same as Oracle Parallel Server.
[2]Equipartitioned table: A partitioned table that has an index that is also partitioned in exactly the same way as the table.