DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

SQL Macros in Oracle Database 21c

#oracle #database #sql #sqlmacros

As you may know, calling a PL/SQL function from a SQL query introduces a context switch between the SQL engine and the PL/SQL engine. This context switching can have a negative impact on query performance.

For example, the following two statements return the same result, but their execution times are significantly different.

Query without using a function

SQL> select sum(sal) from(select salary+60 as sal from person);

  SUM(SAL)

     ———

8891924480

Elapsed: 00:00:00.81
Enter fullscreen mode Exit fullscreen mode

Query using a function

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———

8891924480

Elapsed: 00:00:10.91
Enter fullscreen mode Exit fullscreen mode

Note: The sal_func function is defined as follows:

create or replace function sal_func(sal_param  number)
  return number
is
begin
  return sal_param +60 ;
end;

/
Enter fullscreen mode Exit fullscreen mode

As you can see, the first query takes less than one second to execute, whereas the second query takes approximately 11 seconds.

This comparison might lead us to avoid calling PL/SQL functions from SQL queries. However, avoiding functions altogether can introduce significant maintenance overhead. For example, if a formula needs to be modified, we may have to update multiple parts of an application. Therefore, avoiding function calls in SQL statements is not always the best solution.

To address this performance issue, Oracle introduced SQL Macros in Oracle Database 21c.

SQL Macros allows Oracle Database to expand a function call into its corresponding SQL expression or query during SQL macro expansion. As a result, the SQL statement can be optimized as if the expanded SQL expression had been written directly in the original statement.

For example, Oracle transforms the following query:

select sum(sal) 
from(
select sal_func(salary) as sal 
from person
);
Enter fullscreen mode Exit fullscreen mode

Into the following query:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT SUM(“PERSON”.”SALARY”+60) “SUM(SAL)” FROM “USEF”.”PERSON” “PERSON”
Enter fullscreen mode Exit fullscreen mode

According to Oracle’s feature classification, SQL Macros are a new feature of Oracle Database 21c. However, they were also documented in Oracle Database 20c documentation and are available in Oracle Database 19c starting with Release Update 19.6.

Creating a SQL Macro

To create a SQL Macro, you must specify the SQL_MACRO clause in the function declaration.

The return type of a SQL Macro function must be a character data type, such as VARCHAR2 or CLOB, rather than the data type of the value returned by the expanded SQL expression.

Additionally, the SQL expression or query returned by the function must be enclosed in a quoted string, commonly using Oracle’s alternative quoting mechanism (q’{}’).

For example, we can rewrite the sal_func function as a Scalar SQL Macro:

create or replace function sal_func(sal_param number) 
return varchar2 sql_macro(scalar) is

begin

  return q'{
      sal_param +60
        }’;

end;

/
Enter fullscreen mode Exit fullscreen mode

With this modification, the execution time of the query presented earlier decreases from approximately 11 seconds to just over one second:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———-

8891924480

Elapsed: 00:00:01.11
Types of SQL Macros
Enter fullscreen mode Exit fullscreen mode

Oracle Database supports two types of SQL Macros:

Scalar SQL Macros: Can be used in SQL expressions, including the SELECT list and applicable clauses such as WHERE and HAVING.
Table SQL Macros: Can be used in the FROM clause and return a SQL query that acts as a table expression.
Therefore, the SAL_FUNC function is an example of a Scalar SQL Macro.

The following function, which returns the size (in MB) of the largest segment belonging to a specified schema, is an example of a Table SQL Macro:

create or replace function table_macro_Mesal(p_tab varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{

    select max(bytes/1024/1024) SIZE_MB from
 dba_segments d 
where 
owner =table_macro_Mesal.p_tab

   }’;

end;

/
Enter fullscreen mode Exit fullscreen mode

We can call this Table SQL Macro as follows:

SQL> select * from table_macro_Mesal(‘SYS’);

   SIZE_MB

    ———

    46
Enter fullscreen mode Exit fullscreen mode

Passing Multiple Parameters to a Table SQL Macro

The following function accepts two input parameters:

cnt: The number of segments to return.
typ: the segment type to filter by.
It returns the largest segments based on their size, limited to the specified number of rows.

create or replace function table_macro_Mesal2(cnt number,typ varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{
 select owner, segment_name, bytes / 1024 / 1024 SIZE_MB
   from dba_segments
   where segment_type=table_macro_Mesal2.typ
  order by bytes desc fetch first cnt rows only
   }’;

end;
/
Enter fullscreen mode Exit fullscreen mode

For example, to retrieve the five largest table segments in the database, we can execute:

SQL> select * from table_macro_Mesal2(5,’TABLE’);

OWNER      SEGMENT_NAME                 SIZE_MB

———- ————————- ———-

USEF       JADVAL_TEST                      312

USEF       PERSON                           104

SYS        IDL_UB2$                          16

SYS        WRI$_SQLSET_PLAN_LINES            15

SYS        IDL_UB1$                          12
Enter fullscreen mode Exit fullscreen mode

As demonstrated in these examples, SQL Macros provide a way to encapsulate reusable SQL expressions and queries while allowing the SQL optimizer to work with the expanded SQL statement.

Top comments (0)