SQL Macros: neu in Oracle 21c… und in Oracle 19c!

Veröffentlicht am von

Oracle 21c ist in diversen Cloud-Umgebungen bereits verfügbar. Die on-premises-Version wird für das erste Halbjahr 2021 erwartet – also schon recht bald. Neu und sehr interessant sind SQL Macros.

SQL und PL/SQL sind zwei sehr unterschiedliche Sprachen mit unterschiedlichen Konzepten. Oracle hat grundsätzlich Möglichkeiten eingebaut, die beiden Sprachen zu verbinden. SQLs können in PL/SQL-Code ausgeführt werden und PL/SQL-Funktionen können in SQLs verwendet werden: in der SELECT-Liste in in WHERE-Bedingungen.

Der Aufruf von PL/SQL-Funktionen in SQLs ist meist unproblematisch. Es sei denn, es handelt sich um viele Datensätze und wirklich häufige Aufrufe. Dann stellt man fest, dass das Hin-und-Her-Wechseln zwischen den SQL- und PL/SQL-Engines im Oracle Code recht teuer in Bezug auf die Laufzeit ist.

Hierfür gibt es seit Oracle 12cR1 einen Lösungsansatz, das UDF-Pragma (UDF steht für User-Defined Function). Damit wird beim Übersetzen der PL/SQL-Funktion bereits vorgegriffen und ein Teil der Arbeit erledigt, die sonst zur Laufzeit getan werden muss. Das UDF-Pragma wird uns erhalten bleiben, da hiermit auch komplexe Funktionen erstellt werden können. Die neue Alternative, SQL Macros, sind eher für sehr einfache Code-Stücke gedacht.

SQL Macros gibt es in zwei Ausführungen: SCALAR und TABLE. Beim Typ SCALAR wird ein einzelnder (skalarer) Wert zurück gegeben, wie man ihn z.B. in einer SELECT-Liste nutzen kann. Das SQL Macro wird so definiert, dass es als Rückgabewert einen SQL-Ausdruck liefert. Hier ein einfaches Beispiel:

CREATE OR REPLACE FUNCTION m1 (vin pls_integer)
RETURN VARCHAR2 SQL_MACRO (SCALAR)
AS
BEGIN
    RETURN
    q'{
       TO_CHAR(vin + 1)
    }';
END;
/

Nun kann das SQL Macro in SQL z.B. so verwendet werden:

SELECT count(m1(gencol))
FROM (
SELECT LEVEL gencol
FROM dual
CONNECT BY LEVEL <= 1000000
);

Die Inline-View liefert 1.000.000 Datensätze mit den Zahlen von 1 bis 1.000.000. Daher wird die Funktion m1 1.000.000 Mal aufgerufen.

Vergleicht man die Laufzeit mit der einer „normalen“ PL/SQL-Funktion, so ergibt sich bei Verwendung des SQL Macros eine Reduzierung um mehr als 50%. Wohlgemerkt: Es handelt sich um einen sehr einfachen Test; diese Zahl kann nicht verallgemeinert werden. Der Test wurde übrigens aufdem Always-free Cloud-Angebot der Autonomous Database durchgeführt, da eine On-Premises-Version der Version 21c zum Testzeitpunkt noch nicht zur Verfügung stand.

Die TABLE-Variante der SQL Macros liefert statt eines SQL-Ausdrucks einen SELECT-Befehl zurück. Die Funktion kann wie eine Tabelle oder View in SQL verwendet werden. Dies ermöglicht eine parametrisierte View, da eine PL/SQL-Funktion ja EIngabeparameter haben kann!

Eine gute Nachricht für alle, die bereits die Version 19c einsetzen: Die TABLE-Variante der SQL Macros ist ab Release Update 19.7 auch in 19c verfügbar.

Ein Vortrag zu SQL Macros, der auf den Frankfurter IT-Tagen gehalten wurde, kann hier heruntergeladen werden.