Subqueries sparen mit der WITH-Clause

Es gibt ja so das eine oder andere Feature in umfangreichen Softwareprodukten, das schon geraume Zeit auf dem Markt ist, in der Praxis aber wenig Beachtung findet, obwohl es ein paar nützliche Einsatzgebiete dafür gäbe. Unter Oracle SQL ist so ein Feature nach meiner Erfahrung die “WITH-Klausel”.

Seit Oracle 9i wurde die SQL-Syntax um die WITH-Klausel erweitert. Diese Klausel kann man sich wie eine Materialized View vorstellen, die im Laufe eines SQL-Statements mehrfach referenziert werden kann.

Damit lässt sich in SQL-Code, der bisher mehrere identische Sub-Selects enthielt, der Code deutlich reduzieren (was ihn übersichtlicher und wartungsfreundlicher macht) und damit oft auch Leistung gewinnen, indem diese Sub-Selects weniger ausgeführt werden. Letzteres hängt vom Optimizer ab.

Früher hätte man in solchen Fällen möglicherweise die Ergebnisse einer aufwendigen Unterabfrage in eine temporäre Tabelle ausgelagert und das äußere SQL dann auf die temporäre Tabelle zugreifen lassen. Nun entscheidet sich der Optimizer zwischen dem internen Umschreiben des SQLs oder der temporären Materialisierung.

Beispielszenario

Da ich das Beispiel aus der Oracle-Doku nicht einfach kopieren und lieber Mehrwert liefern will, hier ein etwas unterschiedliches Szenario:

  • Wir wollen aus einer Tabelle “depot_kunde” Daten kopieren, und zwar in Abhängigkeit von zwei Listen in den Tabellen “tmp_uid” und “tmp_dep”.
  • Sind Schlüssel aus dem Datensatz in einer der beiden Listen enhalten, soll der Datensatz kopiert werden.
  • Sind Schlüssel aus dem Datensatz in beiden Listen enhalten, soll der Datensatz nicht kopiert werden.

Dazu gibt es etliche, mögliche Ansätze, hier soll ein nicht so effizienter gezeigt werden, um den Vorteil der WITH-Klausel zu demonstrieren:

INSERT INTO depot_kunde_kopie
SELECT *
  FROM depot_kunde v
 WHERE v.kunde_uid IN( SELECT a.kunde_uid
                         FROM depot_kunde a
                         LEFT OUTER JOIN tmp_uid b ON a.kunde_uid = b.uid
                         LEFT OUTER JOIN tmp_dep c ON a.depot_nr = c.depot_nr
                        WHERE b.uid IS NULL
                           OR c.depot_nr IS NULL )
    OR v.depot_nr IN( SELECT a.depot_nr
                        FROM depot_kunde a
                        LEFT OUTER JOIN tmp_uid b ON a.kunde_uid = b.uid
                        LEFT OUTER JOIN tmp_dep c ON a.depot_nr = c.depot_nr
                       WHERE b.uid IS NULL
                          OR c.depot_nr IS NULL )a

Wie man sieht, ist das Subselect für die Suche nach den Schlüsseln “kunde_uid” und “depot_nr” identisch. Die Untersuchung mit Autotrace für einen Testdatenbestand zeigt 14052419 Consistent Gets an.

Wir lagern das Subselect nun in den WITH-Bereich aus:

INSERT INTO depot_kunde_kopie
WITH sub AS
     ( SELECT a.kunde_uid
            , a.depot_nr
         FROM depot_kunde a
         LEFT OUTER JOIN tmp_uid b ON a.kunde_uid = b.uid
         LEFT OUTER JOIN tmp_dep c ON a.depot_nr = c.depot_nr
        WHERE b.uid IS NULL
           OR c.depot_nr IS NULL )
SELECT *
  FROM depot_kunde v
 WHERE v.kunde_uid IN( SELECT kunde_uid FROM sub )
    OR v.depot_nr IN( SELECT depot_nr FROM sub );

Das Autotrace für den Testdatenbestand zeigt nur noch 2605575 Consistent Gets an, d.h. der Ressourcenbedarf ist an dieser Stelle um den Faktor 5,4 besser!

Wie schon oben erwähnt, entscheidet der Optimizer, wie er mit den Anweisungen in der WITH-Klausel umgehen soll. In etlichen Fällen kann das auch bedeuten, daß die Performance nicht besser wird, möglicherweise sogar schlechter, wie in diesem Blogbeitrag (und dem nachfolgenden) beschrieben. Es empfiehlt sich also, immer Vergleichstests zu machen oder wenigstens den Explain Plan zu vergleichen.

Weiter zu WITH-Clause Reloaded: Hierarchie und Rekursion

Weblinks

Tip: Referencing the Same Subquery Multiple TimeTip: Referencing the Same Subquery Multiple Time

About these ads

3 Gedanken zu „Subqueries sparen mit der WITH-Clause

  1. oraculix Autor

    Bevor mich einer darauf hinweisen wird: Die oben benutzte Bedingung ließe sich noch stark vereinfachen (Stichwort EXISTS). Aber dann müsste ich mir ein neues Beispiel überlegen. ;-)

    Gefällt mir

    Antwort
  2. Pingback: DOAG2011 – Addenda « Oraculix

  3. Pingback: WITH-Clause Reloaded: Hierarchie und Rekursion | Oraculix

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s