Oracle verwendet immer HASH JOIN, auch wenn beide Tabellen riesig sind?

Hash-Joins arbeiten offensichtlich am besten, wenn alles in den Speicher passt. Aber das bedeutet nicht, dass sie nicht immer die beste Join-Methode sind, wenn die Tabelle nicht in den Speicher passt. Ich denke, die einzige andere realistische Join-Methode ist ein Merge-Sort-Join.

Wenn die Hash-Tabelle nicht in den Speicher passen kann, kann die Sortierung der Tabelle für den Merge-Sort-Join auch nicht in den Speicher passen. Und der Merge-Join muss beide Tabellen sortieren. Nach meiner Erfahrung ist Hashing immer schneller als Sortieren, zum Verbinden und zum Gruppieren.

Aber es gibt einige Ausnahmen. Aus dem Oracle®-Handbuch zur Optimierung der Datenbankleistung, dem Abfrageoptimierer :

  

Hash-Joins funktionieren im Allgemeinen besser als Sortier-Joins. Jedoch,   Sortier-Merge-Joins können bessere Ergebnisse erzielen als Hash-Joins, wenn beide   Folgende Bedingungen existieren:

%Vor%

Test

Statt Hunderte von Millionen Zeilen zu erstellen, ist es einfacher, Oracle dazu zu zwingen, nur sehr wenig Speicher zu verwenden.

Dieses Diagramm zeigt, dass Hash-Joins die Merge-Joins übertreffen, auch wenn die Tabellen zu groß sind, um in (künstlich begrenzten) Speicher zu passen:

Hinweise

Für die Leistungsoptimierung ist es normalerweise besser, Bytes als Anzahl der Zeilen zu verwenden. Aber die "echte" Größe der Tabelle ist schwer zu messen, weshalb das Diagramm Zeilen anzeigt. Die Größen reichen von 0,375 MB bis zu 14 MB. Um zu überprüfen, ob diese Abfragen tatsächlich auf den Datenträger geschrieben werden, können Sie sie mit / * + gather_plan_statistics * / ausführen und dann v $ sql_plan_statistics_all abfragen.

Ich habe nur Hash-Joins und Merge-Joins getestet. Ich habe verschachtelte Schleifen nicht vollständig getestet, da diese Join-Methode bei großen Datenmengen immer unglaublich langsam ist. Als eine Vernunftprüfung habe ich es einmal mit der letzten Datengröße verglichen, und es dauerte mindestens einige Minuten, bevor ich es tötete.

Ich habe auch mit verschiedenen _area_sizes, geordneten und ungeordneten Daten und unterschiedlicher Unterscheidbarkeit der Join-Spalte getestet (mehr Übereinstimmungen sind CPU-gebundener, weniger Übereinstimmungen sind mehr IO-gebunden) und haben relativ ähnliche Ergebnisse.

Die Ergebnisse waren jedoch unterschiedlich, wenn die Speichermenge lächerlich klein war. Mit nur 32K sort | hash_area_size war das Zusammenführen von Joins deutlich schneller. Aber wenn Sie so wenig Speicher haben, haben Sie wahrscheinlich größere Probleme zu befürchten.

Es gibt noch viele andere Variablen zu berücksichtigen, wie Parallelität, Hardware, Bloom-Filter usw. Die Leute haben wahrscheinlich Bücher zu diesem Thema geschrieben, ich habe nicht einmal einen kleinen Bruchteil der Möglichkeiten getestet. Aber hoffentlich ist dies genug, um den allgemeinen Konsens zu bestätigen, dass Hash-Joins für große Daten am besten geeignet sind.

Code

Im Folgenden sind die Skripts, die ich verwendet habe:

  

Ich frage mich also, wie ist HASH JOIN möglich, wenn beide Tabellen sehr groß sind?

Es würde in mehreren Durchläufen geschehen: die angetriebene Tabelle wird gelesen und in Blöcken gehackt, die führende Tabelle wird mehrmals gescannt.

Dies bedeutet, dass bei eingeschränktem Speicher Hash-Joins bei O(N^2) skaliert werden, während Merge-Joins bei O(N) skaliert werden (natürlich ohne Sortierung) und bei wirklich großen Tabellen die Hash-Joins übertreffen. Die Tabellen sollten jedoch sehr groß sein, so dass die Vorteile des einzelnen Lesevorgangs Nachteile des nichtsequenziellen Zugriffs ausgleichen würden und Sie alle Daten benötigen würden (normalerweise aggregiert).

Angesichts der Größe von RAM auf modernen Servern sprechen wir über wirklich große Berichte über wirklich große Datenbanken, deren Aufbau Stunden in Anspruch nimmt, nicht etwas, was man im Alltag wirklich sieht.

MERGE JOIN kann auch nützlich sein, wenn das Ausgabe-Recordset auf rownum < N beschränkt ist. Dies bedeutet jedoch, dass die verknüpften Eingaben bereits sortiert sein sollten, was bedeutet, dass sie beide indexiert sind, was bedeutet, dass auch NESTED LOOPS verfügbar ist, und das wird normalerweise vom Optimierer gewählt, da dies effizienter ist, wenn die Verknüpfungsbedingung selektiv ist.

Bei ihren aktuellen Implementierungen sucht MERGE JOIN immer und NESTED LOOPS sucht immer, während eine intelligentere Kombination beider Methoden (unterstützt durch Statistiken) vorzuziehen wäre.

Vielleicht möchten Sie diesen Artikel in meinem Blog lesen:

• Dinge, die SQL braucht: MERGE JOIN das würde suchen