Datenbank 11g macht den FULL OUTER JOIN tauglich für

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