Partitioned Outer Join

Beim Blick auf die nützliche Zusammenfassung der SQL-Optionen für Analyse und Reporting in der Dokumentation ist mir dieser Tage der (in 10g eingeführte) Partitioned Outer Join wieder ins Bewusstsein gekommen, dem ich wohl in der Vergangenheit schon mal begegnet war, damals aber noch nicht so genau wusste, wozu man ihn denn gebrauchen sollte. Da mir das inzwischen klarer ist, hier ein Beispiel:

Gegeben sind eine Fakten-Tabelle mit den jährlichen Umsätzen von Abteilungen (DEPTNO) und eine Datums-Dimension - beide im Beispiel so schmal wie möglich:

drop table d_test;
drop table f_test;

create table d_test (
    year number
);    

insert into d_test(year) values (2010);
insert into d_test(year) values (2011);
insert into d_test(year) values (2012);
insert into d_test(year) values (2013);

create table f_test (
    deptno number
  , year number
  , turnover number
);

insert into f_test (deptno, year, turnover) values (10, 2010, 500);
insert into f_test (deptno, year, turnover) values (10, 2011, 600);
insert into f_test (deptno, year, turnover) values (10, 2012, 500);
insert into f_test (deptno, year, turnover) values (20, 2011, 500);
insert into f_test (deptno, year, turnover) values (20, 2012, 700);

select * from d_test;

      YEAR
----------
      2010
      2011
      2012
      2013

select * from f_test;

    DEPTNO       YEAR   TURNOVER
---------- ---------- ----------
        10       2010        500
        10       2011        600
        10       2012        500
        20       2011        500
        20       2012        700

Um aus diesen Daten einen Bericht zum machen, in dem für jede Abteilung und jedes Jahr ein Datensatz enthalten ist - also Sätze mit einem Turnover = 0 zu ergänzen für alle Abteilungen, zu denen in der Faktentabelle für ein Jahr kein Datensatz vorliegt -, gibt's verschiedene Möglichkeiten. Eine Variante wäre, zunächst eine Referenz als Kreuzprodukt aller Jahre und Abteilungen zu bilden und diese dann per outer join mit den Fakten zu verbinden. Das funktioniert, ist aber relativ sperrig. Nicht möglich ist in diesem Fall der einfache Outer Join:

select r.year
     , t.year
     , t.deptno
     , t.turnover
  from f_test t
 right outer join
       d_test r
    on (t.year = r.year);

      YEAR       YEAR     DEPTNO   TURNOVER
---------- ---------- ---------- ----------
      2010       2010         10        500
      2011       2011         10        600
      2012       2012         10        500
      2011       2011         20        500
      2012       2012         20        700
      2013

Damit wird zwar das in den Fakten fehlende Jahr ergänzt, aber nur einmal und ohne Bezug zu den Abteilungen. Um das gewünschte Ergebnis zu erhalten, muss das Jahr an jede einzelne Abteilung gejoint werden: und genau das leistet der Partitioned Outer Join, bei dem die (zusätzliche Partitions-) Join-Bedingung in einer PARTITION BY-Klausel eingesetzt wird - womit sich PARTITION BY in die Reihe jener Oracle-Begriffe stellt, die je nach Kontext sehr viele unterschiedliche Dinge bezeichnen können ...

select t.deptno
     , t.year
     , r.year
     , t.turnover
  from f_test t partition by (deptno)
 right outer join
       d_test r
    on (t.year = r.year);

    DEPTNO       YEAR       YEAR   TURNOVER
---------- ---------- ---------- ----------
        10       2010       2010        500
        10       2011       2011        600
        10       2012       2012        500
        10                  2013
        20                  2010
        20       2011       2011        500
        20       2012       2012        700
        20                  2013

-- mit etwas Glättung:
select t.deptno
     , r.year
     , coalesce(t.turnover, 0) turnover
  from f_test t partition by (deptno)
 right outer join
       d_test r
    on (t.year = r.year)
 order by t.deptno
        , r.year

    DEPTNO       YEAR   TURNOVER
---------- ---------- ----------
        10       2010        500
        10       2011        600
        10       2012        500
        10       2013          0
        20       2010          0
        20       2011        500
        20       2012        700
        20       2013          0

Hier bekomme ich also die gewünschten acht Ergebnissätze (2 Abteilungen * 4 Jahre). Im Plan sieht man für diesen Fall einen klaren Hinweis auf die durchgeführte Operation:

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    65 |     9  (34)| 00:00:01 |
|   1 |  VIEW                       |        |     1 |    65 |     8  (25)| 00:00:01 |
|   2 |   MERGE JOIN PARTITION OUTER|        |     1 |    52 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN                |        |     4 |    52 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL       | D_TEST |     4 |    52 |     3   (0)| 00:00:01 |
|*  5 |    SORT PARTITION JOIN      |        |     5 |   195 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL       | F_TEST |     5 |   195 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T"."YEAR"="R"."YEAR")
       filter("T"."YEAR"="R"."YEAR")

-- zum Vergleich der Plan für eine Query mit einer 
-- per cartesian join erzeugten Referenz (bei der
-- ich auch noch eine zweite Dimension ergänze und
-- feststelle, dass meine Namensgebung im Test mal
-- wieder nichts taugt ...
create table d_dept (
    deptno number
);

insert into d_dept (deptno) values (10);
insert into d_dept (deptno) values (20);

with
reference as (
select d1.deptno
     , d2.year
  from d_dept d1
     , d_test d2
)
select r.deptno
     , r.year
     , coalesce(t.turnover, 0) turnover
  from f_test t
 right outer join
       reference r
    on (t.year = r.year and t.deptno = r.deptno)
 order by t.deptno
        , r.year;

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     8 |   520 |    12  (17)| 00:00:01 |
|   1 |  SORT ORDER BY          |        |     8 |   520 |    12  (17)| 00:00:01 |
|*  2 |   HASH JOIN OUTER       |        |     8 |   520 |    11  (10)| 00:00:01 |
|   3 |    VIEW                 |        |     8 |   208 |     7   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|        |     8 |   208 |     7   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL  | D_DEPT |     2 |    26 |     3   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |        |     4 |    52 |     4   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL | D_TEST |     4 |    52 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL    | F_TEST |     5 |   195 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."DEPTNO"(+)="R"."DEPTNO" AND "T"."YEAR"(+)="R"."YEAR")

Offensichtlich ist, dass für den Partitioned Outer Join spezielle Schritte erscheinen (MERGE JOIN PARTITION OUTER, SORT PARTITION JOIN) und dass die Cardinality nur im Fall der Query mit Referenz korrekt (= 8) bestimmt wird. Über Hints konnte ich für den Partitioned Outer Join den MERGE JOIN auch in einen NESTED LOOPS umwandeln, allerdings habe ich es auf Anhieb nicht geschafft, einen HASH JOIN daraus zu machen. Auf der Suche nach einer Erklärung dafür, bin ich bei einem recht interessanten Thread (von 2005) in oracle-l gelandet, in dem Christian Antognini die Frage stellt, ob der Partitioned Outer Join die Verwendung der Join-Bedingung als access-Prädikat ausschließt und Lex de Haan die ANSI-Definition der Funktionalität zitiert. Die dort aufgeworfenen Fragen zeigen in jedem Fall, dass das Performance-Verhalten des Partitioned Outer Join ein Thema ist, das sich nicht ohne umfassendere Untersuchung erläutern lässt.

Außerdem habe ich dann auf der Suche nach Aussagen zur Arbeitsweise des Partitioned Outer Join auch noch einen Artikel von Adrian Billington entdeckt, der so ziemlich alles enthält, was ich hier aufgeschrieben habe, und noch ein paar zusätzliche Punkte - anders hätte ich's mir beim Herrn Billington auch nicht vorstellen können.