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 beschrieben. Es empfiehlt sich also, immer Vergleichstests zu machen oder wenigstens den Explain Plan zu vergleichen.
