Datenbank 11g macht den FULL OUTER JOIN tauglich für
Werbung
Arbeiten mit großen Datenbanken Datenbank 11g macht den FULL OUTER JOIN tauglich für das Data Warehouse Autor: Karol Hajdu, Trivadis AG Den FULL OUTER JOIN gibt es in Oracle Database seit der Version 9.0. Doch erst mit der Version 11g steht seinem Einsatz in der Data-Warehouse-Praxis nichts mehr im Wege. Da die meisten Data Warehouses eine Versionierung der Stammdaten implementiert haben, und obwohl diese Versionierung mit einem FULL OUTER JOIN elegant umsetzbar ist, findet dieses Konstrukt in der Praxis keine breite Anwendung. Der Grund liegt darin, dass die Oracle-interne Ausführung eines FULL OUTER JOINS äußerst ineffizient ist. So ist der praktische Einsatz bei großen Tabellen so gut wie unmöglich. Oracle Database 11g bietet eine neue interne Implementation, die diesen Nachteil beseitigt. Die in diesem Artikel beschriebenen Konzepte werden anhand des folgenden Beispiels erklärt: In der Staging-Area des Data Warehouses liegt die Tabelle SA_CUSTOMER. Die Spalte OLTPKey bildet den Primärschlüssel der Tabelle. Zusätzlich gibt es noch eine inhaltliche Spalte namens Status, die den Status des Kunden abbildet. Des Weiteren existiert im CORE des Data Warehouses eine Tabelle C_CUSTOMER mit der Spalte SurrogateKey als Primärschlüssel. Neben der inhaltlichen Spalte Status bestehen noch die beiden versionierungstechnischen Felder C_ValidFrom und C_ValidTo. Abbildung 1 zeigt die Tabellenstruktur und den Tabelleninhalt des Beispiels. Der FULL OUTER JOIN Streng genommen ist der FULL OUTER JOIN ein Mengen-Operator der relationalen Algebra. Die meisten Abbildung 1: Tabellen SA_CUSTOMER und C_CUSTOMER Anbieter der relationalen Datenbank-Technologien haben ihn in den letzten zehn Jahren in ihre DatenbankServer integriert. Im Gegensatz zu einem INNER JOIN liefert ein FULL OUTER JOIN nicht nur solche Rows aus beiden Tabellen zurück, die die gleichen Werte-Kombinationen für die Join-Spalten ausweisen, sondern auch alle restlichen Rows aus beiden Tabellen, in denen keine Übereinstimmung gefunden werden konnte (siehe Abbildung 2). Mit einem FULL OUTER JOIN kann man zum Beispiel die Versionierung der Stammdaten im Data Warehouse elegant umsetzen. Bei der Versionierung geht es darum, die Änderungen seit dem letzten Datenabgleich zu erkennen und dieses ermittelte Delta in geeigneter Form in dem CORE Data Warehouse abzulegen. SELECT SA.*, C.* FROM SA_CUSTOMER SA FULL OUTER JOIN C_CUSTOMER C ON SA.OLTPkey = C.OLTPkey ; Abbildung 2: Unterschied zwischen INNER JOIN und FULL OUTER JOIN 46 www.doag.org Arbeiten mit großen Datenbanken Mit einem FULL OUTER JOIN wird die Delta-Erkennung sehr einfach und lesbar umgesetzt (siehe Listing 1) CREATE TABLE H_CUSTOMER_DELTA AS SELECT * FROM (SELECT C.SurrogateKey , SA.OLTPkey , SA.Status ,CASE WHEN SA.OLTPkey IS NULL THEN 1 ELSE 0 END AS DML_D ,CASE WHEN C.OLTPkey IS NULL THEN 1 ELSE 0 END AS DML_I ,CASE WHEN SA.OLTPkey IS NOT NULL AND C.OLTPkey IS NOT NULL AND (SA.STATUS <> C.STATUS) --Status is mandatory THEN 1 ELSE 0 END DML_U FROM (SELECT * FROM C_CUSTOMER WHERE C_ValidTo = to_date(‚01.01.9999‘, ‚dd.mm.yyyy‘)) C FULL OUTER JOIN SA_CUSTOMER SA ON SA.OLTPkey = C.OLTPkey ) WHERE DML_I+DML_U+DML_D > 0 ; Listing 1: Delta-Erkennung mit Einsatz von FULL OUTER JOIN NLSRTL Version 10.2.0.3.0 – Production SQL> SELECT C.SurrogateKey 2 , SA.OLTPkey 3 , SA.Status 4 ,CASE WHEN SA.OLTPkey IS NULL 5 THEN 1 ELSE 0 END AS DML_D 6 ,CASE WHEN C.OLTPkey IS NULL 7 THEN 1 ELSE 0 END AS DML_I 8 ,CASE WHEN SA.OLTPkey IS NOT NULL 9 AND C.OLTPkey IS NOT NULL 10 AND (SA.STATUS <> C.STATUS) --Status is mandatory 11 THEN 1 ELSE 0 END DML_U 12 FROM (SELECT * FROM C_CUSTOMER 13 WHERE C_ValidTo = to_ date(‚01.01.9999‘, ‚dd.mm.yyyy‘)) C 14 FULL OUTER JOIN SA_CUSTOMER SA 15 ON SA.OLTPkey = C.OLTPkey; Execution Plan -----------------------------------Plan hash value: 3478700539 SQL> SELECT * FROM H_CUSTOMER_DELTA; SURROGA TEKEY OLTPKEY STATUS DML_D DML_I DML_U ----- ------- ------ ----- ----- ---6128 B active 0 0 1 C inactive 0 1 0 734 1 0 0 Listing 2: Resultat der Delta-Erkennung anhand der Beispielsdaten Der FULL OUTER JOIN war bis zur Version 10g praktisch nicht einsetzbar. Den Grund kann man relativ einfach finden, indem man sich den Execution Plan für ein Statement mit FULL OUTER JOIN im Listing 3 anschaut. SQL> select * from v$version ; BANNER --------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production --------------------------------------| Id | Operation | Name | ------------------------------------| 0 | SELECT STATEMENT | | 1 | VIEW | | 2 | UNION-ALL | |* 3 | HASH JOIN OUTER | |* 4 | TABLE ACCESS FULL| C_CUSTOMER | 5 | TABLE ACCESS FULL| SA_CUSTOMER |* 6 | HASH JOIN ANTI | Listing 3: Execution Plan für ein FULL OUTER JOIN in Oracle 9i/10g Man sieht, dass jede der Tabellen zweimal gelesen wird. Solange die Tabellen klein sind, ist dies zu vernachlässigen. Handelt es sich jedoch um große Tabellen, komplexe oder aufwändige Joins, Groupbys oder Sorts, die den Input für den FULL OUTER JOIN aufbereiten, so ist das Resultat verheerend. Eine Verdoppelung der Arbeit hat dann einen fatalen Einfluss auf die Verarbeitungszeit. Dies trifft vor allen für Data-Warehouse-Problemstellungen zu und erklärt somit, warum ein FULL OUTER JOIN in Oracle 9i/10g hier nur sehr selten eine DOAG News Q2-2008 47 Arbeiten mit großen Datenbanken praktische Anwendung fand. Ein Blick in den Execution Plan zeigt die Verbesserungen in Oracle 11g am FULL OUTER JOIN (siehe Listing 4). SQL> select * from v$version BANNER --------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> SELECT C.SurrogateKey 2 , SA.OLTPkey 3 , SA.Status 4 ,CASE WHEN SA.OLTPkey IS NULL 5 THEN 1 ELSE 0 END AS DML_D 6 ,CASE WHEN C.OLTPkey IS NULL 7 THEN 1 ELSE 0 END AS DML_I 8 ,CASE WHEN SA.OLTPkey IS NOT NULL 9 AND C.OLTPkey IS NOT NULL 10 AND (SA.STATUS <> C.STATUS) --Status is mandatory 11 THEN 1 ELSE 0 END DML_U 12 FROM (SELECT * FROM C_CUSTOMER 13 WHERE C_ValidTo = to_ date(‚01.01.9999‘, ‚dd.mm.yyyy‘)) C 14 FULL OUTER JOIN SA_CUSTOMER SA 15 ON SA.OLTPkey = C.OLTPkey; Execution Plan -------------------------------------Plan hash value: 1804996898 ---------------------------------------| Id | Operation | Name | ---------------------------------------| 0 |SELECT STATEMENT | | | 1 | VIEW | VW_FOJ_0 |* 2 | HASH JOIN FULL OUTER| | | 3 | VIEW | | |* 4 | TABLE ACCESS FULL | C_CUSTOMER | | 5 | TABLE ACCESS FULL | SA_CUSTOMER| ----------------------------------------Predicate Information (identified by operation id): ---------------------------------------- 2 - access(„SA“.“OLTPKEY“=“C“.“OLTPKEY“) 4 - filter(„C_VALIDTO“=TO_DATE(‚ 9999-0101 00:00:00‘, ‚syyyy-mm-dd hh24:mi:ss‘)) Listing 4: Execution Plan für ein FULL OUTER JOIN in Oracle Database 11g Die gute Nachricht Oracle hat eine neue Row-Source-Operation implementiert. Sie heißt HASH JOIN FULL OUTER und liest die beiden Input-Tabellen nur einmal. Der praktische Einsatz in Data Warehouses ist daher mit Oracle 11g möglich. Doch wozu dienen die zwei Views – eine mit Namen VW_FOJ_0 und die andere ohne Namen? Diese Views sind nur temporär und im kompilierten Format vorhanden. Vermutlich dienen sie folgendem Zweck: Der Cost Based Optimizer von Oracle verwendet Predicate-Pushing und Subquery-Merging, um einen optimalen Execution-Plan zu finden, der die Daten-Einschränkungen (Filter) möglichst früh appliziert. Für einen FULL OUTER JOIN ist charakteristisch, dass diese Daten-Einschränkungen genau über der Row-Source appliziert werden, die im SELECT aufgeführt ist. Eine frühere oder spätere Applizierung der Filterbedingung, die im Falle vom INNER JOIN die Semantik der Abfrage nicht ändert, hätte im Falle von OUTER JOIN fatale Folgen: Der Execution Plan wäre vielleicht optimal, aber er würde falsche Resultate liefern (Issues bekannt mit Oracle Database 9i Version kleiner als 9.2.0.4). Würde beispielsweise der Filter „C_ValidTo = to_date(‚01.01.9999‘, ‚dd.mm.yyyy‘)“ aus Listing 1 erst nach dem FULL OUTER JOIN appliziert, dann liefert die Abfrage komplett andere Resultate. Findet diese Verschiebung nicht auf Anlass des Benutzers, sondern aufgrund der Optimierung statt (also aus Benutzersicht unbewusst), sind diese Resultate falsch. Hier kommt die Aufgabe der Views zum Tragen: Die Views enthalten vermutlich solche Hints, die dem Optimizer mitteilen, kein Predicate-Pushing oder Subquery-Merging anwenden zu dürfen. Kontakt: Karol Hajdu [email protected] Weitere Informationen zu den Themen der DOAG News finden Sie unter http://www.doag.org/ 48 www.doag.org
