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