NVL-st Du noch oder COALESCE-t Du schon?

NULL-„Werte“ an sich können dem DB-Entwickler genug Kopfzerbrechen bereiten, um alleine damit ganze Artikelserien zu füllen. Hier möchte ich mich lediglich auf einen kleinen Ausschnitt der Problematik beschränken, nämlich der Performance beim Vergleichen und Ersetzen von NULLs. Zum Ersetzen von NULLs in Abfragen wird zumeist die Funktion NVL() verwendet. Diese Funktion hat jedoch zwei entscheidende Nachteile:

1. Sie bietet nur die Möglichkeit, genau einen Ausdruck mit einem anderen zu ersetzen. Will man mehr als zwei Ausdrücke gegeneinander vergleichen, muss die Funktion kaskadiert werden.

2. Der zweite Ausdruck wird immer ausgewertet, auch dann, wenn der erste Ausdruck bereits NOT NULL ist.

Hier ein Beispiel in Oracles HR-Übungsschema, um die möglichen Auswirkungen dieser Nachteile aufzuzeigen: Zur Aufbereitung der Tabelle “employees” sollen alle NULLs durch die Zahl 0 ersetzt werden. Sollte es sich jedoch um einen Verkäufer handeln, soll die standardmäßige Provision von 0,25 % verwendet werden:

SELECT employee_id
     , NVL( commission_pct, DECODE( job_id, 'SA_REP', 0.25, 0 )) comm
  FROM employees;

Wenn wir für dieses SQL ein Trace erzeugen, stellen wir fest, daß das DECODE für jeden Datensatz ausgeführt wird – auch dann, wenn die Spalte “commission_pct” gar nicht NULL ist!

Wenn wir uns nun vorstellen, daß anstelle des trivialen DECODEs ein komplizierteres SELECT oder eine Function steht, die wiederum Abfragen ausführt (z.B. um die Provision in Abhängigkeit des Landes zuzuteilen), wird schnell klar, daß hier sehr viel Arbeit umsonst ausgeführt werden kann.

Kurzschlussreaktion

 Seit Oracle-Version 9i steht die Funktion COALESCE zur Verfügung. Gegenüber NVL besitzt sie folgende Vorteile:

1. COALESCE ermöglicht die Auswertung von mehr als zwei Ausdrücken; der erste, der NOT NULL ist, wird zurückgeliefert.

2. COALESCE Nutzt einen sogenannten “Short Circuit”, d.h., daß die Funktion beendet wird, sobald der erste Nicht-NULL-Ausdruck gefunden wurde. Bei der Abarbeitung von großen Datenmengen ist dies der entscheidende Vorteil gegenüber NVL.

3. COALESCE ist ANSI-SQL-konform.

Das Beispiel von oben in umgeschriebener Form:

SELECT employee_id
     , COALESCE( commission_pct, DECODE( job_id, 'SA_REP', 0.25, 0 )) comm
  FROM employees;

Sonderfall Sequences

Einen Spezialfall möchte ich noch erwähnen (der allerdings auch auf CASE-Operationen zutrifft): Wenn in einem SQL-Ausdruck „Sequence.NEXTVAL“ verwendet wird,  dann wird die Sequence immer hochgezählt — auch, wenn der Ausdruck aufgrund eines Short-Circuits gar nicht ausgewertet werden dürfte! Dies beweist der folgende Code:

SQL> set heading off
SQL> create sequence seq_test;

Sequence created.

SQL> select NVL( NULL, seq_test.nextval ) x from dual;

         1

SQL> select NVL( 9, seq_test.nextval ) x from dual;

         9

SQL> select seq_test.currval from dual;

         2

SQL> select COALESCE( 9, seq_test.nextval ) x from dual;

         9

SQL> select seq_test.currval from dual;

         3

Die Verwendung von COALESCE ermöglicht es also leider nicht, Lücken in Sequences zu vermeiden (was zu NVL-Zeiten schon ein Ärgernis war). Dieses Spezialverhalten bei Sequences ist tief verborgen in der Oracle-Doku erwähnt. Zitat:

Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

• For each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following places:
  • A top-level SELECT statement
  • […]

Fazit:

Es ist nicht verkehrt, komplett von NVL auf COALESCE umzusteigen. Im klassischen NVL-Fall mit nur zwei Ausdrücken, bei denen ein Spalteninhalt gegen einen Literalwert ersetzt wird, mag das Geschmackssache sein. Spätestens, wenn aber komplexere Ausdrücke ins Spiel kommen, sollte COALESCE einfach nur guter Stil sein.

Weblinks

• Oracle-Doku 11gR1: COALESCE
• Scott Wesley hat auf seinem Blog ein anschauliches Beispiel unter Verwendung von dbms_lock.sleep().
• „Does COALESCE short-circuit?“, Probleme unter Oracle 9i: http://code.cheesydesign.com/?p=202
• „True or False – NVL“, ein paar schöne Beispiele zu Mißverständnissen bzgl. NVL()