SQL Macros und was sie können

Ein wesentlicher Grundsatz der Softwareentwicklung ist, den Code wiederverwendbar zu machen um Dopplungen zu vermeiden. Das neue Feature der SQL Macros dient genau diesem Zweck. Denn wie oft kommt es vor, das man identische Ausdrücke in SELECTs oder WHERE Bedingungen oder über mehrere SQLs hinweg immer wieder benötigt. Oder die gleiche Unterabfrage wird immer wieder benötigt, lässt sich aber aufgrund von nötigen Parametern nicht in eine VIEW oder in einen WITH-Clause verpacken. An dieser Stelle setzen die SQL Macros an. Im Prinzip liefert ein SQL Macro ein Stück SQL zurück, das dann an entsprechender Stelle in den eigentlichen Statements verwendet werden kann. Ab 20c gibt es zwei Typen von SQL Macros:

• SCALAR: Liefert einen Ausdruck, der in SELECT, WHERE, HAVING, GROUP BY und ORDER BY verwendet werden kann
• TABLE: Liefert einen Ausdruck zur Verwendung im FROM

Mit dem Release Update 19.7 haben die SQL Table Macros Einzug gehalten. Der wesentliche Vorteil der SQL Macros ist der, dass sie bereits beim Parse des Statements ausgewertet werden. So entsteht eine Art dynamisches SQL, das dann ausgeführt wird. Dem Optimizer stehen also bereits alle nötigen Informationen zur Optimierung zur Verfügung. Normale PL/SQL Funktionen werden im Gegensatz dazu erst zur Ausführungszeit aufgerufen und deren Ergebnisse verwendet. Zum einen verursacht das Context Switche zwischen SQL und PL/SQL und zum anderen kann der Optimizer beim Parse nicht wirklich abschätzen, was innerhalb des PL/SQL Teils passieren wird.

Aber genug der Vorrede, wie wird nun ein SQL Macro erstellt? Als Beispiel muss das SOE Schema aus dem SwingBench herhalten. Dort gibt es unter anderem folgende Tabellen, deren Inhalt noch etwas zu Testzwecken angepasst wird:

SQL> desc warehouses
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 WAREHOUSE_ID                                       NUMBER(6)
 WAREHOUSE_NAME                                     VARCHAR2(35)
 LOCATION_ID                                        NUMBER(4)

SQL> desc inventories
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 WAREHOUSE_ID                              NOT NULL NUMBER(6)
 QUANTITY_ON_HAND                          NOT NULL NUMBER(8)

SQL> desc product_information
 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------
 PRODUCT_ID                                NOT NULL NUMBER(6)
 PRODUCT_NAME                              NOT NULL VARCHAR2(50)
 PRODUCT_DESCRIPTION                                VARCHAR2(2000)
 CATEGORY_ID                               NOT NULL NUMBER(4)
 WEIGHT_CLASS                                       NUMBER(1)
 WARRANTY_PERIOD                                    INTERVAL YEAR(2) TO MONTH
 SUPPLIER_ID                                        NUMBER(6)
 PRODUCT_STATUS                                     VARCHAR2(20)
 LIST_PRICE                                         NUMBER(8,2)
 MIN_PRICE                                          NUMBER(8,2)
 CATALOG_URL                                        VARCHAR2(50)
 
 
 SQL> update warehouses set warehouse_name='Warehouse 1' where warehouse_id=600;

1 row updated.

SQL> update warehouses set warehouse_name='Warehouse 2' where warehouse_id=300;

1 row updated.

SQL> commit;

Commit complete.

Nun soll beispielsweise die Liste aller Produkte eines bestimmten Warehouses generiert werden. Das entsprechende SQL Macro dafür wäre dieses:

SQL> create or replace function product_per_warehouse(p_warehouse_name in varchar2)
  2  return varchar2
  3  SQL_MACRO
  4  is
  5  begin
  6    return q'[
  7  select p.product_name
  8  from product_information p
  9  join inventories i on (p.product_id = i.product_id)
 10  join warehouses w on (i.warehouse_id = w.warehouse_id)
 11  where w.warehouse_name = product_per_warehouse.p_warehouse_name
 12    ]';
 13  end;
 14  /

Function created.

 Man sieht schon, dass ein SQL Macro wie eine normale PL/SQL Funktion erstellt wird, lediglich das Schlüsselwort SQL_MACRO wird ergänzt. Weiterhin sieht man, wie Parameter an das SQL Macro übergeben und darin verwendet werden. Im Gegensatz zu "normalen" PL/SQL Funktionen wird nicht einfach der Parameter per Name referenziert, sondern der Wert des Parameters wird per <SQL Macro Name>.<Parametername> in das SQL Schnipsel eingefügt. In einer SQL Abfrage kann man das SQL Macro nun verwenden:

SQL> select * from product_per_warehouse(p_warehouse_name => 'Warehouse 1');

PRODUCT_NAME
--------------------------------------------------
Lipl5WE iPkN2Mf2J
uS4SZu6nIil8NcmjWtDHVNfLi nO4nNw
[...]

1000 rows selected.

SQL> select * from product_per_warehouse(p_warehouse_name => 'Warehouse 2');

PRODUCT_NAME
--------------------------------------------------
jmvIYV9A  T4sPRiSvGdSFF4PR
 ryLHIzKthOUZOHcO9s0
[...]

1000 rows selected.

Das Ganze kann man noch weiter ausbauen z.B. mit einem Window Clause, der die zurückzugebenden Zeilen beschränkt:

SQL> create or replace function product_per_warehouse(p_warehouse_name in varchar2, p_first_row in number, p_row_count in number)
  2  return varchar2
  3  SQL_MACRO
  4  is
  5  begin
  6    return q'[
  7  select p.product_name
  8  from product_information p
  9  join inventories i on (p.product_id = i.product_id)
 10  join warehouses w on (i.warehouse_id = w.warehouse_id)
 11  where w.warehouse_name = product_per_warehouse.p_warehouse_name
 12  OFFSET product_per_warehouse.p_first_row ROWS
 13  FETCH NEXT product_per_warehouse.p_row_count ROWS ONLY
 14    ]';
 15  end;
 16  /

Function created.

Nun kann man direkt im SQL angeben, welche Zeilen zurückgeliefert werden sollen:

SQL> select * from product_per_warehouse(p_warehouse_name => 'Warehouse 1', p_first_row => 2, p_row_count => 3);

PRODUCT_NAME
--------------------------------------------------
Lipl5WE iPkN2Mf2J
uS4SZu6nIil8NcmjWtDHVNfLi nO4nNw
4JbThtB4k1ea4UtnQB3TynHcYLD5g kG

SQL> select * from product_per_warehouse(p_warehouse_name => 'Warehouse 1', p_first_row => 200, p_row_count => 5);

PRODUCT_NAME
--------------------------------------------------
mDkTZ4e 3WnCUtUQl
CvbrP  RY30Gheb1FIAREEJ1FHm8
ughsQrZ0j3
jmvIYV9A  T4sPRiSvGdSFF4PR
 ryLHIzKthOUZOHcO9s0

SQL> select * from product_per_warehouse(p_warehouse_name => 'Warehouse 2', p_first_row => 560, p_row_count => 4);

PRODUCT_NAME
--------------------------------------------------
5zJU1z3R 8sod3nyj2s3Sx Zxtl8
wvfQU mezI7pCInMFz7sVG7y
BdVJ4HmJg2OaR kA89JJkRFeHewc17G
VFBgwT695aP 8140z

Man sieht also schon an diesen simplen Beispielen, wie mächtig dieses neue Feature sein kann. Das SQL Table Macro ist nicht nur auf einen festen Query Block festgelegt, im Prinzip ist der Block frei definierbar. So kann man beispielsweise auch eine SQL Macro erstellen, das ähnlich den Polymorphic Table Functions verschiedene Tabellen verarbeiten kann. Dazu bedient man sich des Table-Typs aus dem Package DBMS_TF.

SQL> create or replace function count_table_rows(p_table_name in dbms_tf.table_t)
  2  return varchar2
  3  SQL_MACRO
  4  is
  5  begin
  6    return q'[
  7  select count(*) rows#
  8  from p_table_name
  9    ]';
 10  end;
 11  /

Function created.

SQL> select * from count_table_rows(addresses);

     ROWS#
----------
   1500000

SQL> select * from count_table_rows(warehouses);

     ROWS#
----------
      1000

Aber wie sieht man denn nun überhaupt, was am Ende eigentlich ausgeführt wird? Per DBMS_XPLAN sieht man das leider nicht:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  dmwt3hcsyps3t, child number 0
-------------------------------------
select * from count_table_rows(warehouses)

Plan hash value: 3887266715

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |       |       |    35 (100)|          |
|   1 |  VIEW               |            |     1 |    13 |    35   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |            |     1 |       |            |          |
|   3 |    TABLE ACCESS FULL| WAREHOUSES |  1000 |       |    35   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Aber man kann sich mit dem Package DBMS_UTILITY behelfen wobei das Ergebnis vielleicht etwas komplexer aussieht, als man es erwarten würde.

SQL> set serveroutput on
SQL> declare
  2    l_clob clob;
  3  begin
  4    dbms_utility.expand_sql_text (
  5      input_sql_text  => q'[
  6       select * from count_table_rows(addresses)
  7   ]',
  8      output_sql_text => l_clob  );
  9    dbms_output.put_line(l_clob);
 10  end;
 11  /
SELECT "A1"."ROWS#" "ROWS#" FROM  (SELECT "A3"."ROWS#" "ROWS#" FROM  (SELECT COUNT(*) "ROWS#" FROM  (SELECT "A2"."ADDRESS_ID" "ADDRESS_ID","A2"."CUSTOMER_ID" "CUSTOMER_ID","A2"."DATE_CREATED"
"DATE_CREATED","A2"."HOUSE_NO_OR_NAME" "HOUSE_NO_OR_NAME","A2"."STREET_NAME" "STREET_NAME","A2"."TOWN" "TOWN","A2"."COUNTY" "COUNTY","A2"."COUNTRY" "COUNTRY","A2"."POST_CODE"
"POST_CODE","A2"."ZIP_CODE" "ZIP_CODE" FROM "SOE"."ADDRESSES" "A2") "A4") "A3") "A1"

Bleibt zum Schluss noch die Frage, wie man SQL Macros im Data Dictionary wiederfindet. Denn eigentlich sind die Macros ja nur PL/SQL Funktionen. Dazu gibt es eine MOS Note "How To Identify the SQL Macros in Oracle Data Dictionary 19.7 Onwards (Doc ID 2678637.1)", in der das erklärt wird.  

SQL> select u.name OBJECT_OWNER, o.name OBJECT_NAME, properties2,
  2  CASE
  3  -- forwards compatibility with 20c++
  4  when bitand(properties2, 16) > 0 OR properties2 = 0 THEN 'TABLE'
  5  when bitand(properties2, 32) > 0 THEN 'SCALAR'
  6  END sql_macro_type
  7  from obj$ o
  8  join user$ u on (o.owner# = u.user#)
  9  join procedureinfo$ pi on (o.obj# = pi.obj#)
 10* WHERE bitand(pi.properties,2147483648) > 0
SQL> /

OBJECT_OWNER                   OBJECT_NAME                    PROPERTIES2 SQL_MA
------------------------------ ------------------------------ ----------- ------
SOE                            PRODUCT_PER_WAREHOUSE                    0 TABLE
SOE                            COUNT_TABLE_ROWS                         0 TABLE

Alles in allem sind SQL Macros also ein sehr mächtiges neues Feature und werden in Zukunft sicherlich des öfteren zum Einsatz gebracht.