Data Densification using Partition Outer Joins in Oracle

Often reports are required to show data that is available and also “fill gaps” where data is unavailable. In other words this is also called Data Densification. Part of this can be achieved by the use of the Outer Joins. But this approach too sometimes falls short of the requirements. Oracle introduced the Partition Outer Joins in version 10g which can be used for such requrements as demonstrated below.

Let’s take an example to illustrate.

Imagine two tables,
1. Quarter – Listing the different quarters in an year (Q1 to Q4)
2. Sales – Listing of Sales by different customers (Jai, Tejas & Krishnam) in different quarters of the year. Data is recorded only for those customers/quarters where sales > 0

Tables - Quarter & Sales

Tables – Quarter & Sales

Now let’s try to get all the customers’ sales for all the quarters. First, let’s try with the LEFT OUTER JOIN method.

Data retrieved using the Left Outer Join approach

Data retrieved using the Left Outer Join approach

This gets the data for all the quarters (LEFT OUTER JOIN on table Quarter), but only where there are sales recorded. For Q4 there are no sales for any customers and hence a row with NULL and 0. But our requirement was to get data for all customers for all the quarters, i.e., 4 quarters * 3 customers = 12 rows.

Let’s use the PARTITION OUTER JOIN method.

Data Retrieved using the Partition Outer Join approach

Data Retrieved using the Partition Outer Join approach

Done !!

Advertisements

~ by Jayanthan Ravi on October 7, 2014.

2 Responses to “Data Densification using Partition Outer Joins in Oracle”

  1. Partition is very useful..It took some time to get the hang of it.

    • True.. Most of the ‘new features’ in oracle usually take some time to get used to. I still havent completely gotten used to the ANSI standard of query writing 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: