The Context

Last week at work I got a task to stop using a deprecated table in one of our PL/SQL procedures. Instead, I was to use a much larger and harder-to-query collection of tables, which were guaranteed to have the freshest data. The deprecated table was being filled by transfer scripts written decades ago, and while I could have just turned the relevant parts into a materialized view, I wanted to avoid the hassle of maintaining this across different databases and instead wanted to try my luck at writing an efficient query that takes advantage of already existing indexes on these tables.

Just like the ancient transfer scripts, the PL/SQL packages my team was now in charge of are plagued by layers and layers of bad design decisions. Now I'm not particularly religious, but reading that code makes me think of Jesus' quote "Father, forgive them, for they do not know what they are doing". Luckily for you, I won't be showing any of that code in this post. Essentially, there was one big for-loop where a series of queries were executed for every row in a table. These queries were all accessing the deprecated table and it was my job to re-write them in a performant matter.

Discalimer

My tests were done on Oracle version 19c. Perhaps things have changed at the time of you reading this post.

TL;DR

• Functions that return an SQL_MACRO cannot be defined in the same package body where they are called. In my opinion this makes them near useless.
• Using SQL macros in often-executed queries incurs a significant performance penalty through query re-compilation.

Compilation Problems

So after a lot of trial and error, I managed to re-write the query SELECT * FROM DEPRECATED_TABLE as a very large, albeit performant query accessing other tables. Seeing as this large query was about to be executed many places inside a for-loop, I was looking for a way to define it once and then just re-use it. A function could have sufficed, but I wanted to keep all of the logic inside the well-optimized SQL execution context, instead of jumping out to PL/SQL which can be considerably slower. This led me to discover SQL_MACRO, which on the surface sounds exactly like what I need - a way to define a fragment of an SQL query once and have it "injected" by the compiler everywhere I need it. "No jumping out to PL/SQL code!" - is what I falsely assumed.

Let's just get straight into it: you cannot call an SQL_MACRO function in the same package it is defined:

CREATE OR REPLACE PACKAGE MacrosSuck IS

    FUNCTION myFunc() RETURN integer;
    FUNCTION macro() RETURN varchar2 SQL_MACRO;

END;
    CREATE OR REPLACE PACKAGE BODY MacrosSuck IS

    FUNCTION macro() RETURN varchar2 SQL_MACRO IS
    BEGIN
        return 'SELECT * FROM SOME_TABLE';
    END;

    FUNCTION myFunc() RETURN integer IS
    res integer;
    BEGIN
        select sum(value)
        into res
        from   macro();
        return vResult;
    END;
END;

ORA-62565: The SQL Macro method failed with error(s).
ORA-04067: not executed, package body <package body name> does not exist

In my opinion, this is straight up a design flaw that makes SQL Macros practically unusable. Of course, there is absolutely no mention of this in Oracle's official documentation. I'm sure there is some kind of explanation as to why this doesn't work, but it should - plain and simple. Oracle should have limited the logic inside a function returning an SQL_MACRO to allow the compiler to inline and compile such queries as the package's body is being compiled.

If you really want or need to use SQL Macros, you'll have to define them in another procedure, or as free-standing functions, but this leads to other problems as I'll explain in the next section.

Performance Hit

I've already mentioned that we were using a for-loop to iterate through rows of a table which is a clear anti-pattern, however there was no way I could refactor this monstrosity in one commit so I decided to consolidate several queries bound by PL/SQL variables into one big but performant SQL mega-query, and it would reference my Macro fragment.

When I ran the new procedure, it took more than twice as long to run on our dev DB - 5 minutes compared to 2. I took out the new mega-query and compared its runtime to one of the fragments I consolidated in my re-write. Sure enough, my mega-query was slower, 25ms on average compared to around 5, but I thought that the gains of removing PL/SQL code should have made up for this difference. I was about to give up and reach for the easy fix of materialized views, but I thought I'd try one more thing - inlining the macro fragments instead of actually calling the function.

Suddenly, the procedure was running around 30% faster than the old version! This made it clear how in my case, the overhead of compiling the query each time it's executed in a loop is detrimental to performance. I mean, it makes sense when you think about it, as SQL_MACRO is not a real compile-time macro, the query has to be stitched together and evaluated every time it's run. Now, as the function must be defined outside of the package's body, the compiler has to treat it as something dynamic, to be called every single time. This could have been mitigated by having the SQL_MACRO function defined together with the calling query.

Closing Thoughts

All in all, I don't see a common use-case for SQL Macros in their current state. If you have a different opinion or see some errors in this post, please write a comment below!

Shoutout to Paulzip and SQLORA for being the only two blogs where I could find any information regarding the actual use of SQL_MACRO. As with all things Oracle DB - these niche blogs is where you'll find the actually useful information.