The preview version of Oracle 20c is now available in the cloud and I got the chance to test some new features there. I start with SQL macros, a feature that I think could well become a kind of game changer: let’s just look at the long-awaited parameterized views.
Motivation and Definition
So what are SQL Macros and why do we need them? Oracle’s definition of SQL Macros (SQM) is that they are used to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. But don’t we already have some tools to do that? Views, PL/SQL functions, Common Table Expressions (CTE, aka WITH clause) to name a few. Well, we do, but there are often drawbacks with their use. You can make your SQL more structured, readable and maintainable using WITH clauses but you probably have to repeat them across different queries. Of course, you can avoid this by encapsulating SQL logic in views but you cannot easily make views parameterized.
And then, of course, we have PL/SQL functions as reusable, parameterized constructs. At my customers I see quite often how PL/SQL functions are “overused”. Functions for everything, in turn calling SQL in those functions to keep the SQL statements simple. It is very difficult to find a good balance between simplicity and performance. Keep in mind that every invocation of a PL/SQL Function in a SQL statement means context switch and can have a huge impact on performance (using a pragma UDF may reduce the effect but not eliminate it completely). If you are calling SQL inside a called function, you will be switching twice.
And that’s one of the problems addressed with SQL Macros. SQL Macros are the functions where you can encapsulate you recurring logic, but rather than executing the function at run time, providing the result and causing a context switch, these functions will be executed once at parse time and return a piece of SQL code which will be incorporated in your SQL query. No context switch at all.
And this is not the only advantage of using SQL macros. So let’s take a closer look at what types are there, how they can be created and used.
Basics
What is SQL Macro? It is simply a function that returns text (CHAR, VARCHAR2 or CLOB) with a new SQL_MACRO annotation. This text becomes a part of your SQL right where the function was invoked. So where in a SQL statement can a macro substitution take place? Everywhere where you can call functions: in SELECT, WHERE, GROUP BY, HAVING and FROM clauses.
There are two types of SQL macros: they are either SCALAR expressions or TABLE expressions. SCALAR macros are only allowed and make sense in SELECT, WHERE, GROUP BY and HAVING clauses. TABLE macros can only be used in FROM clause. We will see the differences as we go on, but now let’s start with a simple example.
Let’s say you are selecting from EMP and want to know how many years an employee is working for the company now:
SELECT e.*
, FLOOR(MONTHS_BETWEEN (SYSDATE, e.hiredate)/12) as years
FROM emp e;
If you have to do it quite often, probably you will end up with a virtual column or will hide the complex expression inside a user defined PL/SQL function, again causing a context switch. Yet another way to do this in Oracle 20c is to define a SQL macro:
CREATE OR REPLACE FUNCTION job_duration RETURN VARCHAR2
SQL_MACRO(SCALAR) AS
BEGIN
RETURN q'! FLOOR(MONTHS_BETWEEN (SYSDATE, hiredate)/12) !';
END;
/
We just define the function which returns the desired expression and make the new annotation (line 2) . We have to provide the type of the macro here (SCALAR) , because the default type is TABLE.
SQL> SELECT ename
, job_duration as years
FROM emp e
WHERE job_duration > 38
ENAME YEARS
---------- ----------
SMITH 39
ALLEN 39
WARD 39
3 rows selected.
Just call this function in your SQL and it works! Note how we are using it also in the WHERE clause for filtering.
Note again, the calculation didn’t take place inside of our function, but rather at runtime of our SQL statement. Thus we are not only avoiding context switch but also exposing the business logic to query optimizer and let it do its job.
How do I know what’s going on?
For testing and debugging it is quite important to see the real SQL running, isn’t it? Is it a problem? Let’s try explain plan. Well, we can see something that looks like our WHERE predicate, but there is no macro substitution in a SQL:
SQL> select * from dbms_xplan.display_cursor()
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c9dyry6wy14kx, child number 1
-------------------------------------
SELECT ename , job_duration as years FROM emp e WHERE
job_duration > 38
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(FLOOR(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("HIREDATE
"))/12)>38)
20 rows selected.
What I supposed will work, is to use SQL text expansion from DBMS_UTILITY. This worked indeed for more complicated cases with table macros but not in this simplest case.
DECLARE
l_clob CLOB;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => q'!
SELECT ename
, job_duration as years
FROM emp e
WHERE job_duration > 38!',
output_sql_text => l_clob );
DBMS_OUTPUT.put_line(l_clob);
END;
/
PL/SQL procedure successfully completed.
SELECT "A1"."ENAME" "ENAME","ONFTEST"."JOB_DURATION"() "YEARS" FROM "ONFTEST"."EMP" "A1" WHERE "ONFTEST"."JOB_DURATION"()>38
So I have yet to find a way to see a real SQL statement in this case or Oracle should fix something. Anyway I think it is quite important.
Next steps
I could already test some interesting use cases, which are more practical and go beyond the “basics”, like:
- building hash keys/hash_diffs hiding the whole complexity in a macro
- building parameterized views
- doing a “temporal” join
I can’t wait to write about it in the following posts!