Table and scalar SQL macros hands-on

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.

sql_macros01
sql_macros01

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

About Post Author

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>