Kostenlose Joins

Randolf Geist präsentiert in seinem Blog ein interessantes Beispiel, in dem ein über Nested Loops verarbeiteter Outer Join mit einer Join-Bedingung, die auf der Seite der driving table eine Spalte anspricht, die immer NULL ist, vom CBO als nahezu kostenlos betrachtet wird: die Gesamtkosten der Query entsprechen denen des Zugriffs auf die driving table und der CBO addiert nur die Kosten eines einzigen Lookups, da er - aus guten Gründen - annimmt, dass die Lookups keine Treffer liefern können. Allerdings weist Randolf darauf hin, dass die Loops offenbar trotzdem ausgeführt werden, was die Entscheidung, die Kosten weitgehend zu ignorieren, doch ein wenig fragwürdig erscheinen lässt. Hier die entsprechenden rowsource Statistiken meiner Windows8-Spieldatenbank (11.2.0.1):

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |   1000K|00:00:01.54 |   82063 |
|   1 |  NESTED LOOPS OUTER          |        |      1 |   1000K|   1000K|00:00:01.54 |   82063 |
|   2 |   TABLE ACCESS FULL          | T1     |      1 |   1000K|   1000K|00:00:00.73 |   82063 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2     |   1000K|      1 |      0 |00:00:00.55 |       0 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |   1000K|      1 |      0 |00:00:00.26 |       0 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."NULL_FK"="T2"."ID")

Demnach fällt für step 3 und 4 Arbeit an (wie die Starts und A-Time anzeigen), allerdings offenbar nicht im Bereich der Lesezugriffe (Buffers = 0). Aber immerhin erkennt der CBO, dass es sich hier um einen Sonderfall handelt, der eine korrigierte Kalkulation verdient.

In einem zweiten Artikel "Cost-free" joins - 2 untersucht Randolf Geist das Verhalten von MERGE Joins, die der CBO bekanntlich relativ selten auswählt, da sie in der Regel das Sortieren beider Datenquellen erfordern (sofern diese nicht schon durch einen vorangehenden Schritt - etwa einen INDEX RANGE SCAN - sortiert sind). Interessant ist der Hinweis, dass die Reihenfolge der Datenquellen (anders als beim HASH JOIN) keine größere Rolle spielt und dass es nicht möglich ist, diese Reihenfolge zu ändern (ebenfalls anders als beim HASH JOIN). Der Fall, in dem ein kostenloser Join auftritt, ist hier ein MERGE Outer Join einer sehr großem Tabelle, die mit einer Lookup-Tabelle verknüpft wird, auf die ein INDEX UNIQUE SCAN erfolgt - wobei der Effekt problematisch wird, sobald Parallelisierung ins Spiel kommt. Aber da der Fall etwas unübersichtlich ist, kopiere ich an dieser Stelle einfach das Fazit des Artikels:

For MERGE JOINs there are some special cases where the current costing model doesn't properly reflect the actual work - together with some strange behaviour of the MERGE JOIN code when using Parallel Execution this can lead to questionable execution plans preferred by the optimizer.

Carefully check the resulting execution plans when using Parallel Execution and MERGE JOINs get preferred by the optimizer.