Partition joins

A few weeks ago (OK, in June; time flys when you are old!) I posted a piece of time series aggregation

One technique I did not mention (and nobody reminded me of it ;-) ) was “partition joins”. This was a new feature introduced in Oracle 10g. Perhaps, the main reason I did not mention it was that I had never implemented it in DW before – most of my DW customers are on Oracle 9.2 and the few on 10g had no need for it.

According to the Oracle 10 Data Warehousing Guide Partition Joins have uses in “filling in the gaps” in sparse data sets. As the partition join uses outer joins the “missing” data is represented by NULLS (which of course can be changed by using an NVL function if a computed value is required.)

Of course, filling in the gaps is not necessary if we are just calculating cumulative sales for a period (sum() works just fine) but if we need to date base comparisons such as cumulative sales against same date last year filling the gaps in essential.

However, the real question is ‘how does this approach compare to other techniques’ I will try to benchmark this and write an update soon