Der kostenbasierte Oracle Optimizer ermittelt beim Zugriff auf zwei oder mehrere Tabellen in einem SELECT-Befehl, welche Tabelle zuerst gelesen und somit zur €žtreibenden Tabelle€œ wird und in welcher Reihenfolge die weiteren Tabellen hinzugenommen werden. Entscheidend dabei ist auch die Methode zur Zusammenführung der Teilmengen der Tabellen. Dem Oracle Optimizer stehen zur Umsetzung von Join-Operationen drei unterschiedliche Strategien zu Verfügung, welche im Folgenden dargestellt werden.
Nested Loops Join
Einen Nested Loops Join kann man sich wie eine geschachtelte FOR()- oder WHILE()-Schleife in der konventionellen Programmierung vorstellen. Die Rückgabewerte der treibenden Tabelle werden abgearbeitet und für jeden Rückgabewert wird die andere Tabelle bzw. deren Index einmal durchlaufen.
Vorteile:
- Die ersten Ergebnisse des Nested Loops Join können bereits an den Benutzer zurückgeliefert werden, obwohl die Schleife noch nicht vollständig abgearbeitet ist.
- Wenn ein niedriger Prozentsatz der in den Tabellen gespeicherten Sätze ausgegeben werden soll, wird der Indexzugriff besonders effizient.
- Bevorzugte Strategie bei der Optimierung im Hinblick auf die bestmögliche Antwortzeit (first_rows).
- Für den Join selbst ist keine teure Sortierung der Daten notwendig (nur bei der Benutzung von DISTINCT, GROUP BY, ORDER BY etc.). Eine Sortierung würde je nach pga_aggregate_target oder sort_area_size Einstellungen Platz im Hauptspeicher oder nach dessen Aufbrauchen den langsameren Disk-Zugriff vom TEMP-Tablespace benötigen.
Nachteile:
- Wird ineffektiv wenn keine Indexe für die Join-Spalten existieren.
- Kann zu mehr Disk-Reads als bei einem Merge oder Hash Join führen. Wenn kein Index existiert müsste schlimmstenfalls (wenn der Cache für die Tabelle zu klein ist) für jeden Rückgabewert der treibenden Tabelle die andere Tabelle von der Disk gelesen werden. Disk-Reads sind im Vergleich zum Prüfen von Indizes teuer, da sie deutlich langsamer sind als der Hauptspeicher.
Sort-Merge Join
Die Tabellen werden zuerst nach den Schlüsselspalten der Join-Spalten sortiert, das Ergebnis kann dann mit einem einmaligen Scan durch beide sortierten Relationen berechnet werden.
Vorteile:
- Sehr effizient, wenn ein hoher Prozentsatz der in den Tabellen gespeicherten Sätze ausgegeben werden soll und dadurch der Indexzugriff ineffizient wird.
- Wenn keine Sortierung notwendig ist, da schon ein sortierter Index existiert, ist der Sort-Merge Join schneller als ein Hash Join, da das erstellen der Hash-Tabelle entfällt. Die Kosten betragen dann nämlich nur jeweils einen Lesevorgang für beide Tabellen.
- Effizient bei der Optimierung im Hinblick auf den Gesamtdurchsatz (all_rows).
Nachteile:
- Die Resultate werden nicht unmittelbar an den Benutzer geliefert, da vorher die Sortierung erfolgen muss.
- Der Sort benötigt Hauptspeicher und Disk-Space.
Hash Join
Aus dem Resultset der kleineren Tabelle wird zunächst eine Hashtabelle im Hauptspeicher erstellt. Dazu werden über eine Hashfunktion Hashwerte aus den Attributen der Join-Spalten gebildet. Der Hashwert wird dann an einer durch seinen Wert festgelegten Stelle (Bucket) in der Hashtabelle abgelegt.
Die größere Tabelle dient nun als treibende Tabelle. Satz für Satz werden ihre relevanten Attribute ebenfalls über die Hashfunktion berechnet. Dadurch kann der passende Bucket der Hashtabelle bestimmt werden, in welchem geprüft wird, ob ein entsprechender Wert der kleineren Tabelle vorhanden ist. Ãœber den passenden Hashwert ist dann die Bestimmung des gesuchten Datensatzes möglich.
Vorteile:
- Sehr effizient und meistens die mit Abstand schnellste Strategie, wenn der Größenunterschied der Tabellen bedeutend ist und für die kleinere Tabelle ausreichend Hauptspeicher vorhanden ist. Die Kosten betragen dann nämlich abzüglich des Hashens nur jeweils einen Lesevorgang für beide Tabellen.
- Effizient bei der Optimierung im Hinblick auf den Gesamtdurchsatz (all_rows).
- Für den Join selbst ist keine teure Sortierung der Daten notwendig (nur bei der Benutzung von DISTINCT, GROUP BY, ORDER BY etc.).
Nachteile:
- Die Effizienz hängt von der Größe des zur Verfügung stehenden Platzes im Hauptspeicher ab. Wenn Teile der Hashtabelle geswappt werden müssen, kann die Performance sehr leiden, da Teile der Hashtabelle immer wieder von der langsameren Disk gelesen werden müssen. Daher meist nur bei kleineren Tabellen (oder wenn eine kleine Tabelle mit einer großen Tabelle gejoint wird) effizient.
- Kann nicht bei Wertebereichen (Konditionen wie <, <=, > und =>) verwendet werden, da der gehashte Wert der treibenden Tabelle mit denen der Hashtabelle nur auf Gleichheit geprüft werden kann.
- Wenn eine Sortierung über ein DISTINCT, GROUP BY, ORDER BY etc. sowieso erforderlich wird, macht ein Sort-Merge Join mehr Sinn.
- Die Resultate werden nicht unmittelbar an den Benutzer geliefert, da zuerst die Hashtabelle erstellt werden muss.
Join Operation Hints
Wenn man dem Oracle Optimizer bei der Wahl der richtigen Strategie für die Join-Operation nicht traut und selber die beste Strategie erzwingen oder durch Tests ermitteln möchte, kann man die Wahl über Hints manipulieren.
Die Definition von Hints geschieht folgendermaßen: SELECT /*+ Hint */ €¦ FROM €¦
| Hint | Bemerkung |
| USE_NL(Tabelle1 [Tabelle2€¦]) | Der Nested Loops Join wird verwendet. |
| USE_NL(@Block Tabelle1 [Tabelle2€¦]) | |
| USE_NL_WITH_INDEX(Tabelle1 [Tabelle2€¦]) | Der Nested Loops Join wird verwendet, wenn mindestens eine der Tabellen den erforderlichen Index besitzt. |
| USE_NL_WITH_INDEX(@Block Tabelle1 [Tabelle2€¦]) | |
| NOT_USE_NL(Tabelle1 [Tabelle2€¦]) | Der Nested Loops Join wird verhindert. |
| NOT_USE_NL(@Block Tabelle1 [Tabelle2€¦]) | |
| USE_MERGE(Tabelle1 [Tabelle2€¦]) | Der Merge Join wird verwendet. |
| USE_MERGE(@Block Tabelle1 [Tabelle2€¦]) | |
| NOT_USE_MERGE(Tabelle1 [Tabelle2€¦]) | Der Merge Join wird verhindert. |
| NOT_USE_MERGE(@Block Tabelle1 [Tabelle2€¦]) | |
| USE_HASH(Tabelle1 [Tabelle2€¦]) | Der Hash Join wird verwendet. |
| USE_HASH(@Block Tabelle1 [Tabelle2€¦]) | |
| NOT_USE_HASH(Tabelle1 [Tabelle2€¦]) | Der Hash Join wird verhindert. |
| NOT_USE_HASH(@Block Tabelle1 [Tabelle2€¦]) |
Quellen (Stand 29.11.2010):
J. Ahrends (2005): Oracle10g für den DBA, S. 609ff.
J. Gennick (2004: Oracle SQL*Plus: the definitive guide, S. 396f.
M. Gurry (2002): Oracle SQL-Tuning: kurz & gut, S. 60ff.
S. Haas (2006): Oracle Tuning in der Praxis, S. 247f.
http://oracle-online-help.blogspot.com/2007/03/nested-loops-hash-join-and-sort-merge.html
http://www.dba-oracle.com/t_oracle_sorting.htm
http://www.geekinterview.com/question_details/15261