SQL Macros in 21c (backported to 19.6 onwards in 19c)

 

SQL Macros provides the benefits of Pl/SQL to abstract the complexity of logic, while eliminates context switches between SQL and Pl/SQL processing engine.

Example to re-write the PL/SQL into SQL Macros Scalar Expression:

-- PL/SQL Version
create or replace function get_full_name
        (first varchar2,last varchar2)
return varchar2 is 
begin
    return first||','||last;
end;
/

-- SQL Macros Version
create or replace function get_full_name_sqm
        (first varchar2,last varchar2)
return varchar2 sql_macro(scalar) is 
begin
    return q'(first||','||last)';
end;
/

Example to re-write the View into SQL Macros Table Expression. In this way, Oracle Optimizer can transform original code into optimal execution plans.

-- View Version
create or replace view v_employees
as
select employee_id,salary,
        avg(salary) over(partition by department_id) avg_salary 
from hr.employees;

-- SQL Macros Version
create or replace function f_employees
return varchar2 sql_macro is
begin
    return q'(
        select employee_id,salary,
        avg(salary) over(partition by department_id) avg_salary 
        from hr.employees
    )';
end;
/

SQL> explain plan for select * from v_employees where employee_id=107;

Explained.

SQL> select  * from table(dbms_xplan.display(format => 'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3819948069

-----------------------------------------------------------------------------------
| Id  | Operation	    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |		  |   107 |  4173 |	4  (25)| 00:00:01 |
|*  1 |  VIEW		    | V_EMPLOYEES |   107 |  4173 |	4  (25)| 00:00:01 |
|   2 |   WINDOW SORT	    |		  |   107 |  1177 |	4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1177 |	3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / "V_EMPLOYEES"@"SEL$1"
   2 - SEL$2
   3 - SEL$2 / "EMPLOYEES"@"SEL$2"

Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "EMPLOYEES"@"SEL$2")
      NO_ACCESS(@"SEL$1" "V_EMPLOYEES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('21.1.0')
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=107)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22],
       "V_EMPLOYEES"."SALARY"[NUMBER,22], "V_EMPLOYEES"."AVG_SALARY"[NUMBER,22]

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
   2 - (#keys=1; rowset=256) "DEPARTMENT_ID"[NUMBER,22],
       "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], AVG("SALARY") OVER (
       PARTITION BY "DEPARTMENT_ID")[22]
   3 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22],
       "DEPARTMENT_ID"[NUMBER,22]

Query Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]
  SEL$2 (PARSER) [FINAL]

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------


56 rows selected.

SQL> explain plan for select * from f_employees() where employee_id=107;

Explained.

SQL> select  * from table(dbms_xplan.display(format => 'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 720055818

---------------------------------------------------------------------------------
| Id  | Operation	    | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |		|   107 |  4173 |     4  (25)| 00:00:01 |
|*  1 |  VIEW		    |		|   107 |  4173 |     4  (25)| 00:00:01 |
|   2 |   WINDOW SORT	    |		|   107 |  1177 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  1177 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$3 / "SYS__$"@"SEL$2"
   2 - SEL$3
   3 - SEL$3 / "EMPLOYEES"@"SEL$3"

Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$3" "EMPLOYEES"@"SEL$3")
      NO_ACCESS(@"SEL$F5BB74E1" "SYS__$"@"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$2" >"SEL$1")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      OUTLINE_LEAF(@"SEL$3")
      ALL_ROWS
      DB_VERSION('21.1.0')
      OPTIMIZER_FEATURES_ENABLE('21.1.0')

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SYS__$"."EMPLOYEE_ID"=107)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

   1 - (rowset=256) "SYS__$"."EMPLOYEE_ID"[NUMBER,22],
       "SYS__$"."SALARY"[NUMBER,22], "SYS__$"."AVG_SALARY"[NUMBER,22]
   2 - (#keys=1; rowset=256) "DEPARTMENT_ID"[NUMBER,22],
       "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22], AVG("SALARY") OVER (
       PARTITION BY "DEPARTMENT_ID")[22]
   3 - (rowset=256) "EMPLOYEE_ID"[NUMBER,22], "SALARY"[NUMBER,22],
       "DEPARTMENT_ID"[NUMBER,22]

Query Block Registry:
---------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

  SEL$1 (PARSER)
    SEL$F5BB74E1 (VIEW MERGE SEL$1 ; SEL$2) [FINAL]
  SEL$2 (PARSER)
  SEL$3 (PARSER) [FINAL]


61 rows selected.