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.