Table of contents
Preamble
SQL macros (SQM) is a new simpler solution to embed business logic processing rules and outsource them into PL/SQL functions. These particular PL/SQL functions are then called SQL macros or SQM for short. You then use those SQM is simplified SQL statements for increased clarity in code.
You can find two types of SQM:
- SCALAR expressions can be used in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clauses
- TABLE expressions used in a FROM-clause
Those SQM have been introduced in 20c (in Oracle Cloud only I think) and finally released in 21c. They have been backported 19c with 19.7 Release Update (RU), but only for the table SQM. The scalar SQM remains a 21c only feature.
The first time I have heard about this feature I told myself: what’s the point ? We can already all(most) do this in PL/SQL ! First for table SQM it is not that easy and what I didn’t have in mind is the potential huge impact of context switch between SQL and PL/SQL.
SQM have this huge advantage to be able to be injected in the original (or outer) SQL query and Oracle optimizer can arrange the query as a whole for better parsing. And by design there is NO context switches between SQL and PL/SQL.
Not to be limited by the Oracle release I have decided to do my testing on 21c (21.4) on a Linux 7.9 Redhat box. My server has 64GB of memory and 12 cores.
To have some sample figures I have installed the Oracle Sample Data Models.
Table SQL Macros
In the OE schema (order entry data model) let say you want to compute the amount of each order. Yes there is a column but I would not have personally added it like this as it is a complex process to keep it always updated.
With old legacy you would start by creating a view that will return the total mount per order like (the ORDER BY is obviously optional):
SQL> CREATE OR REPLACE VIEW orders_amount AS SELECT orders.order_id, SUM(oi.unit_price * oi.quantity) AS order_total FROM oe.orders, oe.order_items oi WHERE orders.order_id=oi.order_id GROUP BY orders.order_id ORDER BY orders.order_id; VIEW created. SQL> SELECT * FROM orders_amount FETCH FIRST 5 ROWS only; ORDER_ID ORDER_TOTAL ---------- ----------- 2354 46257 2355 94513.5 2356 29473.8 2357 59872.4 2358 7826 |
But what if now I tell you I want to be able to retrieve the order amount only for an order I pass as a parameter ? Views cannot be used anymore… Of course you could create a pipelined function and do the trick but would it be as efficient as table SQM ?
Inspired from what I have already done on pipelined function I have created below code (again, the function is wrapped into a package to manage the output type):
CREATE OR REPLACE PACKAGE orders_pkg AS TYPE output IS TABLE OF VARCHAR2(50); FUNCTION amount_pipelined(ord_id NUMBER) RETURN output pipelined; END; / CREATE OR REPLACE PACKAGE BODY orders_pkg AS FUNCTION amount_pipelined(ord_id NUMBER) RETURN output pipelined IS CURSOR cursor01 IS SELECT orders.order_id, SUM(oi.unit_price * oi.quantity) AS order_total FROM oe.orders, oe.order_items oi WHERE orders.order_id=oi.order_id AND orders.order_id=ord_id GROUP BY orders.order_id; item01 cursor01%ROWTYPE; BEGIN OPEN cursor01; LOOP FETCH cursor01 INTO item01; EXIT WHEN cursor01%notfound; pipe ROW(item01.order_id || ' ' || item01.order_total); END LOOP; CLOSE cursor01; RETURN; END; END orders_pkg; / |
The table SQM counterpart is much simpler:
CREATE OR REPLACE FUNCTION orders_amount_sqm(order_id NUMBER) RETURN VARCHAR2 sql_macro(TABLE) IS BEGIN RETURN q'{select orders.order_id, sum(oi.unit_price * oi.quantity) as order_total from oe.orders, oe.order_items oi where orders.order_id=oi.order_id and orders.order_id=orders_amount_sqm.order_id group by orders.order_id}'; END; / |
First the pipelined function:
SQL> SET TIMING ON autotrace ON SQL> SET lines 200 SQL> SELECT * FROM TABLE(orders_pkg.amount_pipelined(2354)); COLUMN_VALUE -------------------------------------------------- 2354 46257 Elapsed: 00:00:00.00 Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 2536557059 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| AMOUNT_PIPELINED | 8168 | 16336 | 29 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ STATISTICS ---------------------------------------------------------- 3 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo SIZE 580 bytes sent via SQL*Net TO client 52 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 1 ROWS processed |
Second the table SQM. I am expecting a decrease in recursive calls that collect SQL – PL/SQL context switch:
SQL> SET TIMING ON autotrace ON SQL> SET lines 200 SQL> SELECT * FROM orders_amount_sqm(2354); ORDER_ID ORDER_TOTAL ---------- ----------- 2354 46257 Elapsed: 00:00:00.00 Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 1332326505 ------------------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | | 1 | SORT GROUP BY NOSORT| | 1 | 16 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 13 | 208 | 3 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 4 | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | ORDER_ITEMS | 13 | 156 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - ACCESS("ORDERS"."ORDER_ID"=2354) 4 - filter("OI"."ORDER_ID"=2354) STATISTICS ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo SIZE 655 bytes sent via SQL*Net TO client 52 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 1 ROWS processed |
No recursive call and we even don’t see the function execution as the whole SQL statement has been replaced by the query we have designed in the SQM ! This is a simple example but we might even see more differences while testing scalar SQM.
Scalar SQL Macros
Opposite to table SQM here the idea is to have something scaler and so inserted, for the example I have chosen, in a SELECT statement. My idea is to compute on the fly the total amount of an order, the order id will be passed as a unique parameter. I have prepared the legacy PL/SQL way of working with:
CREATE OR REPLACE FUNCTION orders_amount_legacy(ord_id NUMBER) RETURN NUMBER IS result NUMBER; BEGIN SELECT SUM(oi.unit_price * oi.quantity) INTO result FROM oe.orders, oe.order_items oi WHERE orders.order_id=oi.order_id AND orders.order_id=ord_id; RETURN result; END; / |
the scalar SQM version is:
CREATE OR REPLACE FUNCTION orders_amount_sqm(ord_id NUMBER) RETURN VARCHAR2 sql_macro(scalar) IS BEGIN RETURN '(select sum(oi.unit_price * oi.quantity) from oe.orders, oe.order_items oi where orders.order_id=oi.order_id and orders.order_id=ord_id group by orders.order_id)'; END; / |
remark:
I initially planned to return a NUMBER from this scalar SQM but this is not (yet ?) supported:
LINE/COL ERROR -------- ----------------------------------------------------------------- 2/8 PLS-00776: SQL macro can only return character return types |
This time I select all orders and compute for each the total amount (traceonly to avoid displaying result). The legacy PL/SQL version first:
SQL> SET TIMING ON autotrace traceonly SQL> SET lines 200 SQL> SELECT order_id, orders_amount_legacy(order_id) FROM orders ORDER BY order_id; 105 ROWS selected. Elapsed: 00:00:00.01 Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 2880579201 ----------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 105 | 420 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | ORDER_PK | 105 | 420 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- STATISTICS ---------------------------------------------------------- 105 recursive calls 0 db block gets 743 consistent gets 0 physical reads 0 redo SIZE 3054 bytes sent via SQL*Net TO client 118 bytes received via SQL*Net FROM client 8 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 105 ROWS processed |
The scalar SQM version:
SQL> SET TIMING ON autotrace traceonly SQL> SET lines 200 SQL> SELECT order_id, orders_amount_sqm(order_id) FROM orders ORDER BY order_id; 105 ROWS selected. Elapsed: 00:00:00.01 Execution PLAN ---------------------------------------------------------- PLAN hash VALUE: 4080764090 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 105 | 420 | 109 (0)| 00:00:01 | | 1 | SORT GROUP BY NOSORT | | 1 | 19 | 2 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 19 | 2 (0)| 00:00:01 | | 3 | VIEW | VW_GBF_6 | 1 | 7 | 0 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 4 | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| ORDER_ITEMS | 1 | 12 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | ITEM_ORDER_IX | 1 | | 1 (0)| 00:00:01 | | 7 | INDEX FULL SCAN | ORDER_PK | 105 | 420 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 4 - ACCESS("ORDERS"."ORDER_ID"=:B1) 6 - ACCESS("OI"."ORDER_ID"=:B1) filter("ITEM_1"="OI"."ORDER_ID") STATISTICS ---------------------------------------------------------- 0 recursive calls 0 db block gets 238 consistent gets 0 physical reads 0 redo SIZE 3051 bytes sent via SQL*Net TO client 118 bytes received via SQL*Net FROM client 8 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 105 ROWS processed |
The recursive calls is simply 0 for the scalar SQL Macro version while I have quite a lot for the legacy PL/SQL version. We also see in the scalar SQM version that the explain plan is completely different. This is explained by the SQM that is injected in original query and then optimized by the Oracle optimizer.
Conclusion
Really pleased by the feature, the table SQL Macros is really interesting as it resolves parameter limitation of traditional views. And at same time, needless to say, they are much simpler to implement than pipelined functions.
Scalar SQL macros are really much more efficient from SQL – PL/SQL context switches point of view this is obvious.
From my perspective I anyway feel you have much less control in what you are authorized to do in a SQL macros than you would do with a traditional PL/SQL function. But wherever you can use SQL macros just go for it…
There is a thread on stackoverflow on the limitation of SQL macros for further reading…
References
- SQL Macros (SQM)
- SQL Macros in Oracle Database 21c
- Oracle Scratchpad – SQL Macro
- How To Identify the SQL Macros in Oracle Data Dictionary 19.7 Onwards (Doc ID 2678637.1)
