Issue Statement
As you may know, invoking a function in a query results in a context switch between the SQL engine and the PL/SQL engine, which negatively impacts performance. For example, the two statements below produce the same output but have significantly different execution times.
A Common Case
Execution without using a function:
SELECT view_name
FROM all_views
WHERE text_length = (SELECT MAX (text_length)
FROM all_views
WHERE owner = 'HR');
VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:0.568
Execution using a function:
Execution using a function:
SELECT view_name
FROM all_views
WHERE text_length = fnc_get_max_length ('HR');
VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:03.84
Note: The function get_max_length is defined as follows:
CREATE OR REPLACE FUNCTION fnc_get_max_length (pi_owner IN VARCHAR2)
RETURN NUMBER
IS
v_length NUMBER;
BEGIN
SELECT MAX (text_length)
INTO v_length
FROM all_views
WHERE owner = pi_owner;
RETURN v_length;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
/
Issue Statement/ Solution
As observed, the first query executes in less than a second, while the second query takes nearly 4 seconds. This comparison suggests avoiding functions in queries, but in some cases, not using functions can be costly, requiring changes to various parts of the program for a single formula change. Therefore, avoiding functions in queries isn’t always the best solution.
Oracle introduced SQL Macros in version 21c to address the mentioned performance issue. Using this feature, the query containing the function is rewritten in a simple form without using the function.
Solution
According to Oracle’s classification, SQL Macros is a new feature in version 21c, although it was introduced in Oracle 20c documentation and has been usable since version 19.6.
To use SQL Macros in a function, the SQL_MACRO keyword must be used in the function’s creation statement, and the return type can only be a string type (string, varchar2, clob, etc.). The return value must also be enclosed in the {}.
For example, the function get_max_length_sql_macro is rewritten using SQL Macros as follows:
CREATE OR REPLACE FUNCTION fnc_get_max_length_sql_macro (
pi_owner IN VARCHAR2)
RETURN VARCHAR2
SQL_MACRO(SCALAR)
IS
v_length NUMBER;
BEGIN
RETURN q'{
SELECT
MAX(text_length)
FROM
all_views
WHERE
owner = pi_owner
}';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
With the above changes, the execution time of the query used at the beginning of the text changes from about 8 seconds to less than 1 second, approximately 0.7 seconds:
SELECT view_name
FROM all_views
WHERE text_length = fnc_get_max_length_sql_macro('HR');
VIEW_NAME
---------
EMP_DETAILS_VIEW
Elapsed: 00:00:0.74
Type of SQL Macros
SQL Macros are of two types: Table macros and Scalar macros. The Scalar macro function is placed in the SELECT list, WHERE clause, HAVING clause of an SQL statement, whereas Table SQL macros are used in the FROM clause.
Therefore, the function fnc_get_max_length_sql_macro is considered a Scalar SQL macro.
Type of SQL Macros-Table Macro
Additionally, the following function, with two input parameters (cnt(number) and pi_owner(varchar2)), is a table macro designed to return the list of views related to the called schema in descending order of the largest query:
CREATE OR REPLACE FUNCTION fnc_table_macro_all_views (cnt NUMBER,
pi_owner VARCHAR2)
RETURN VARCHAR2
SQL_MACRO(TABLE)
IS
BEGIN
RETURN q'{
select owner,view_name,text_length from all_views
where owner = table_macro_all_views.pi_owner
order by text_length desc fetch first table_macro_all_views.cnt rows only
}';
END;
Table Macros
Using the function in a query:
SELECT * FROM fnc_table_macro_all_views (5, ‘SYS‘);
OWNER VIEW_NAME TEXT_LENGTH
--------- ------------------------- -----------
SYS _user_stat 51594
SYS ALL_CUBE_DEPENDENCIES 29829
SYS ALL_IND_STATISTICS 24463
SYS ALL_TAB_PARTITIONS 23892
SYS USER_OBJ_AUDIT_OPTS 23772
SQL Macros
By leveraging SQL Macros, you can significantly improve the performance of your SQL queries while maintaining the flexibility and maintainability of using functions.