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.
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.
Keine Kommentare:
Kommentar veröffentlichen