Partitioned Join

Partitioned Outer Joins in Oracle

From 10g onwards Oracle has had a type of join called a Partitioned Outer Join, providing a convenient syntax which can be used to fill gaps in sparse data.
The documentation describes the result of a partitioned outer join as “a UNION of the outer joins of each of the groups in the logically partitioned table with the table on the other side of the join.”

To demonstrate what this means in practice, I will run through a simple example.

We will start with two very simple tables, products and sales, which look like this:

Click on the lines below to expand and view the source for creating and populating the tables.

We now have some data spread over a small range of dates. There are gaps in the series of dates, as well as dates for which there are multiple rows.

We can produce a summary showing total sales of each product per day.

Execution Plan

Notice that we have no bananas? (Of which we haven’t sold any in this period.)
How do we include the full set of products on each date, with a total of zero for a product that didn’t sell?

Without partitioned joins, we would end up writing something like this:

Execution Plan

Partitioned outer joins allow us to perform the same “densifying” of our data, but with a more convenient syntax and a more efficient execution plan.

Execution Plan

You’ll notice that although we have filled some of the gaps and now have all the products for each of the days where there was a sale made, we still have gaps in the date range. In order to fill those gaps, we will need a list of all the dates through the range we wish to report on.
You can write a query to do this (and put the results in a table), but to keep things simple here I am just going to insert a small series into a table manually.

If we want sales for all products on every date, then that is the Cartesian product as provided by a cross join:

But, do we really need the bananas?

If asked to exclude from the report any product that has never sold, then we might end up adding a semi-join to the query.
We still have filled our gaps in the date range for products that have been selling, but exclude any that have no sales.

Execution Plan

The same results can also be produced without adding the semi-join to the query by using a partitioned outer join.

Execution Plan

If this has piqued your interest, and you are ready for some more advanced examples, then you’ll find this and much, much more over at the Oracle Data Warehousing Guide.

Leave a Reply