Oracle partitioning and table joins

Oracle Tips by Burleson Consulting

QUESTION:  Is there any benefit to having partitioned tables for SQL joins?  In know that partition pruning helps full scans, but what about table joins?

ANSWER: Yes, Oracle offers partition-wise table joins, but they only apply to merge joins and hash joins, not nested loop joins.  Table joins with a low cardinality (just a few matching rows as a percentage of the original table rows) will not benefit from partition-wise joins. The Oracle 10g docs note:

"Using the partitioning methods described in this section can help you tune SQL statements to avoid unnecessary index and table scans (using partition pruning).

You can also improve the performance of massive join operations when large amounts of data (for example, several million rows) are joined together by using partition-wise joins."

"Partition-wise Joins: Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. Partition-wise joins can be applied with two tables are being joined together, and both of these tables are partitioned on the join key.

Partition-wise joins breaks a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution. "