Difference between Oracle Macro and UDF

Question

What is the difference between creating a 'normal' UDF in Oracle and one that is a macro? For example, for the macro, they give an example of:

CREATE FUNCTION date_string(dat DATE) 
                    RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
   RETURN q'{
             TO_CHAR(dat, 'YYYY-MM-DD')
          }';
END;
/

And for a function.

What would be a use-case where a macro would be more useful than a normal function? And when a function would be better used that a macro? Does one ever have either performance benefits or limitations over the other?

Answer 1

The SQL & PL/SQL languages have separate runtime engines. This means every time a SQL statement calls a PL/SQL UDF, there's a context switch (and vice-versa).

While each switch is fast, calling a PL/SQL function thousands or millions of times in a SQL statement can make it significantly slower.

SQL macros work differently. At parse time the database resolves the expression to become part of the statement. It searches for the parameter names in the return string. Then effectively does a find/replace of these with the text of whatever you've passed for these parameters.

For example, if you run:

select date_string ( date_col ) 
from   some_table;

The final SQL statement is effectively:

select to_char ( date_col, 'yyyy-mm-dd' )
from   some_table;

This means there's no runtime context switch. This can lead to good performance gains, for example:

create function date_string_macro ( dat date ) 
   return varchar2 sql_macro(scalar) is
begin
   return q'{ to_char(dat, 'yyyy-mm-dd') }';
end;
/

create function date_string_plsql ( dat date ) 
   return varchar2 is
begin
   return to_char(dat, 'yyyy-mm-dd' );
end;
/

declare
  start_time pls_integer;
begin
  start_time := dbms_utility.get_time ();
  for rws in (
    select *
    from   dual
    where  date_string_plsql ( sysdate + level ) > '2021'
    connect by level <= 1000000
  ) loop
    null;
  end loop;
  dbms_output.put_line ( 
    'PL/SQL runtime = ' || ( dbms_utility.get_time () - start_time ) 
  );
  
  start_time := dbms_utility.get_time ();
  for rws in (
    select *
    from   dual
    where  date_string_plsql ( sysdate + level ) > '2021'
    connect by level <= 100000
  ) loop
    null;
  end loop;
  dbms_output.put_line ( 
    'Macro runtime  = ' || ( dbms_utility.get_time () - start_time ) 
  );
end;
/

PL/SQL runtime = 570
Macro runtime  = 54

Around 10x faster in this case!

Because the expression becomes part of the SQL statement, as a side benefit the optimizer has full visibility of the underlying expression. This may lead to better execution plans.

This enables you to get the code-reuse benefits of PL/SQL functions (e.g. common formulas, string formatting, etc.) with the performance of pure SQL.

So why not make all existing PL/SQL function macros?

There are a couple of other important differences between them.

First up, order of argument evaluation. PL/SQL uses application-order, macros use normal-order. This can lead to behaviour differences in some cases:

create or replace function first_not_null ( 
  v1 int, v2 int
) 
  return int as
begin
  return coalesce ( v1, v2 );
end first_not_null;
/

select first_not_null ( 1, 1/0 ) from dual;

ORA-01476: divisor is equal to zero

create or replace function first_not_null  ( 
  v1 int, v2 int
) 
  return varchar2 sql_macro ( scalar ) as
begin
  return ' coalesce ( v1, v2 ) ';
end first_not_null;
/

select first_not_null ( 1, 1/0 ) from dual;

FIRST_NOT_NULL(1,1/0)   
                       1 

Secondly - and more importantly - resolving the expression only happens in SQL. If you call a SQL macro in PL/SQL, it returns the string as-is:

exec dbms_output.put_line ( first_not_null ( 1, 0 ) );

coalesce ( v1, v2 ) 

Finally SQL macros also have a table variant. This allows you to create template queries you can pass tables and columns to.

I've discussed table macros in several blog posts, showing how you can use table macros to write generic top-N per group functions and reusable CSV-to-rows functions