Lesson 8	Partition-wise Queries and Joins
Objective	Describe 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 Oracle Partition-wise Join

1) This diagram shows the concept of partition-wise joins. When two partitioned table are joined, the matching partitions are processed together and all the resulting queries are executed parallel. The database server must have certain parameters set to allow parallel queries, a prerequisite for partition-wise joins.

2) This shows the first parameter you need to set up your database server in preparation for partition-wise joins. Set PARALLEL_AUTOMATIC_TUNING=TRUE. Setting this parameter to true allows the database server to automatically tune parallel queries for you. It greatly simplifies the setup of your system.

3) This shows the second parameter you need to set up your database server in preparation for partition-wise joins. Set PARALLEL_THREADS_PER_CPU to a number from 2 to 8. For processors with fast I/O connections, use 2 (the default setting) and allow the server to use connection load balancing to speed up prcoessing. For processors with slower I/) connections, use up to 8 to help speed up performanc.

4) The partitioned tables that are joined in a query must have certain characteristics to be eligible for the partition-wise join. First both partitioned tables must be equipartitioned on the same partition key. In the example show here the CUSTOMER_ORDER table and the CUSTOMER_SALES table both have partitioning based on the CUST_ID column.

5) Both tables must be partitioned in exactly the same ranges. For example the CUSTOMER_ORDER table has four partitions on the CUST_ID, and each partition has a 1000 number range (0-999, 1000-1999, etc.) To enable a partition-wise join on the CUSTOMER_SALES table, that table must also have four partitions on the CUST_ID, with each partition using the same range.

6) Finally, the query must have the partition key as the join criteria between the two tables. Continuing with the example, you see a query that joins the CUSTOMER_ORDER and CUSTOMER_SALES tables with a join on the CUST_ID column. The CUST_ID column is the partition key in both partitioned tables.

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.