SQL Macro is finally here! – Keep Learning Keep Living

Hi,

I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send a parameter and use it to choose which select statement runs and I am kind of able to do that but not I expected. I have read about the documentation but not much detail. I will mentioned about some “odd” staff that I found about sql macro at the end of this post.

And this could help us about the conditions of: “where customer_name = nvl(:Cust_name, customer_name”. I think many of you know this and suffer it right 🙂 if parameter is null then bring all of them but if it is not null then just bring matched rows.

EDIT: bummer! thanks to Mike Cutz from oracle community, showed me a mistake on my performance compare below. while I was testing, I used constant ‘EMPLOYEES’ value instead of bind variable. so I am changing that part.

So, what is sql macro. Sql macro is an SQL add-on to enhance the sql capability. mostly we use PLSQL when we need to do something complex and can not do it just by sql but this cause “context switch”. you can think this as, there are two engines in Oracle, one is SQL and the other PLSQL. two engines does not do other’s job. if a plsql statement requires to run an SQL then statements is sent to SQL engine and if sql engine finds a plsql (a function in select statement for ex) then it is sent to plsql engine to run. going back and forward between these engines is called “context switch” and context switch is a performance ENEMY! So we always try to do our job in just one engine but sometimes we have to use both. This is a new feature for 20c but I am running those code on 19.6.

SQL Macro will reduce the requirement of PLSQL so there won’t be context switch and we will have more performance. Also, it does help about getting better execution plans for complex queries some how. There are 2 types of SQL Macro, Scalar and Table. default is table and I will demonstrate table SM here.

Table SQL Macro works and looks like pipelined functions but remember, this is run by sql engine not by plsql.

create table tmp as select * from all_objects;
create index i_tmp1 on tmp(object_name);
create index i_tmp2 on tmp(object_id);
create index i_tmp3 on tmp(data_object_id);
create index i_tmp4 on tmp(created, object_name);
create index i_tmp5 on tmp(owner, created);

exec dbms_stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);

create or replace function sf_test(p_objecT_name varchar2) return varchar2 SQL_MACRO as
begin

  return 'select * from tmp
          where object_name = sf_test.p_object_name';  
end;
/

create table tmp as select * from all_objects;

create index i_tmp1 on tmp(object_name);

create index i_tmp2 on tmp(object_id);

create index i_tmp3 on tmp(data_object_id);

create index i_tmp4 on tmp(created, object_name);

create index i_tmp5 on tmp(owner, created);

exec dbms_stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);

create or replace function sf_test(p_objecT_name varchar2) return varchar2 SQL_MACRO as

begin

return 'select * from tmp

where object_name = sf_test.p_object_name';

end;

I just created a dummy table and created SF_TEST sql macro. sf_test looks like just a regular function with one varchar2 parameter but at the definition we use SQL_MACRO before “as” keyword. and then just return a string which has a select statement. here is usage:

select * from sf_Test('EMPLOYEES');

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
HR     EMPLOYEES                                        75462...
OE     EMPLOYEES                                        75518...

select * from sf_Test('EMPLOYEES');

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

HR EMPLOYEES 75462...

OE EMPLOYEES 75518...

as you see our sql macro worked as a pipelined function and returned object information (TMP table) with named EMPLOYEES. basically it is just running the select statement in the sql macro and this operation is done in sql engine (well at least mostly…).

let’s make another example:

create or replace function sf_fetch_n_rows (p_number_of_rows number) return varchar2 sql_macro as
begin
  return 'select * 
          from tmp
          fetch first sf_fetch_n_rows.p_number_of_rows rows only';  
end;
/

select * from sf_fetch_n_rows(2);

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...

select * from sf_fetch_n_rows(4);

OWNER  OBJECT_NAME                    SUBOBJECT_NAME    OBJECT_ID...
SYS    TS$                                              16...
SYS    ICOL$                                            20...
SYS    C_FILE#_BLOCK#                                   8...
SYS    I_OBJ2                                           37...

create or replace function sf_fetch_n_rows (p_number_of_rows number) return varchar2 sql_macro as

begin

return 'select *

from tmp

fetch first sf_fetch_n_rows.p_number_of_rows rows only';

end;

select * from sf_fetch_n_rows(2);

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

SYS TS$ 16...

SYS ICOL$ 20...

select * from sf_fetch_n_rows(4);

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID...

SYS TS$ 16...

SYS ICOL$ 20...

SYS C_FILE#_BLOCK# 8...

SYS I_OBJ2 37...

cool right? I can parameterized the number of rows to return from an sql statement.

Let’s take it further:

create or replace function sf_fetch_n_rows_from_any_table (p_number_of_rows number, p_table dbms_tf.TABLE_T) return varchar2 sql_macro as
begin
  return 'select * 
          from sf_fetch_n_rows_from_any_table.p_table
          fetch first sf_fetch_n_rows_from_any_table.p_number_of_rows rows only';  
end;
/

select * from sf_fetch_n_rows_from_any_table(4, hr.employees);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE           JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         2003-06-17 00:00:00 AD_PRES         24000                                      90
        101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         2005-09-21 00:00:00 AD_VP           17000                       100            90
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         2001-01-13 00:00:00 AD_VP           17000                       100            90
        103 Alexander            Hunold                    AHUNOLD                   590.423.4567         2006-01-03 00:00:00 IT_PROG          9000                       102            60

create or replace function sf_fetch_n_rows_from_any_table (p_number_of_rows number, p_table dbms_tf.TABLE_T) return varchar2 sql_macro as

begin

return 'select *

from sf_fetch_n_rows_from_any_table.p_table

fetch first sf_fetch_n_rows_from_any_table.p_number_of_rows rows only';

end;

select * from sf_fetch_n_rows_from_any_table(4, hr.employees);

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------

100 Steven King SKING 515.123.4567 2003-06-17 00:00:00 AD_PRES 24000 90

101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 00:00:00 AD_VP 17000 100 90

102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 00:00:00 AD_VP 17000 100 90

103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 00:00:00 IT_PROG 9000 102 60

this is really cool! we can pass a “table” as parameter (which is come with polymorphic table functions actually) to our sql macro and run our query based on this table. in my example I passed 4 (number of rows) and “hr.employees” table. please be careful, hr.employees is not a varchar2 variable! this is not a string concatenation operation. Now we can get any rows from any table:

select * from sf_fetch_n_rows_from_any_table(1, DBA_CONNECTION_TESTS);

P CONNECTION_TEST SQL_CONNECTION_TEST    SERVICE_NAME E
- --------------- ----------------------------------- -
Y SQL_TEST        SELECT 1 FROM DUAL                  Y

select * from sf_fetch_n_rows_from_any_table(3, hr.departments);

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700

select * from sf_fetch_n_rows_from_any_table(1, DBA_CONNECTION_TESTS);

P CONNECTION_TEST SQL_CONNECTION_TEST SERVICE_NAME E

- --------------- ----------------------------------- -

Y SQL_TEST SELECT 1 FROM DUAL Y

select * from sf_fetch_n_rows_from_any_table(3, hr.departments);

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

20 Marketing 201 1800

30 Purchasing 114 1700

So, let’s get back to performance enhancement. Actually I am not sure about the exact result but I run a few simple test and it showed, execution plans are more accurate. here is an example:

set autotrace traceonly

var p_object_id number;
var p_object_name varchar2(128);
var p_object_type varchar2(128);
var p_owner varchar2(128);

exec :p_object_name := 'EMPLOYEES';

    select *
    from tmp
    where object_id = nvl(:p_object_id,object_id)
      and object_name = nvl(:p_object_name,object_name)
      and object_type = nvl(:p_object_type,object_type)
      and owner = nvl(:p_owner,owner);

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2707160492

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     2 |   962 |   377   (2)| 00:00:01 |
|   1 |  VIEW                                  | VW_ORE_A06AD640 |     2 |   962 |   377   (2)| 00:00:01 |
|   2 |   UNION-ALL                            |                 |       |       |            |          |
|*  3 |    FILTER                              |                 |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| TMP             |     1 |   133 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_TMP2          |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    FILTER                              |                 |       |       |            |          |
|*  7 |     TABLE ACCESS FULL                  | TMP             |     1 |   133 |   375   (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter(:P_OBJECT_ID IS NOT NULL)
   4 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))
   5 - access("OBJECT_ID"=:P_OBJECT_ID)
   6 - filter(:P_OBJECT_ID IS NULL)
   7 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND
              "OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

set autotrace traceonly

var p_object_id number;

var p_object_name varchar2(128);

var p_object_type varchar2(128);

var p_owner varchar2(128);

exec :p_object_name := 'EMPLOYEES';

select *

from tmp

where object_id = nvl(:p_object_id,object_id)

and object_name = nvl(:p_object_name,object_name)

and object_type = nvl(:p_object_type,object_type)

and owner = nvl(:p_owner,owner);

2 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2707160492

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 962 | 377 (2)| 00:00:01 |

| 1 | VIEW | VW_ORE_A06AD640 | 2 | 962 | 377 (2)| 00:00:01 |

| 2 | UNION-ALL | | | | | |

|* 3 | FILTER | | | | | |

|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TMP | 1 | 133 | 2 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | I_TMP2 | 1 | | 1 (0)| 00:00:01 |

|* 6 | FILTER | | | | | |

|* 7 | TABLE ACCESS FULL | TMP | 1 | 133 | 375 (2)| 00:00:01 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(:P_OBJECT_ID IS NOT NULL)

4 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND

"OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

5 - access("OBJECT_ID"=:P_OBJECT_ID)

6 - filter(:P_OBJECT_ID IS NULL)

7 - filter("OBJECT_NAME"=NVL(:P_OBJECT_NAME,"OBJECT_NAME") AND

"OBJECT_TYPE"=NVL(:P_OBJECT_TYPE,"OBJECT_TYPE") AND "OWNER"=NVL(:P_OWNER,"OWNER"))

now, I make my first query a little bit complex with using some NVL functions and bind variables. now I use 4 bind variables (owner, object_name, object_type, object_id) but only OBJECT_NAME has a value and others are null. also please remember I created and index on OBJECT_NAME on TMP table. so in this query I am looking for all objects with name EMPLOYEES but since Oracle needs to check all values if they are null or not, execution plan becomes more complex and cost is higher. the equivalent version of this query (with this bind variable values) is:

Select * from tmp where object_name = ‘EMPLOYEES’;

and this is a very simple query which can use the index on TMP table.

EDIT: I deleted this part and rewriting it please see my previous edit at top.

I will discuss about reading variable values in sql macro after that but as a sneak peek, you can not read any variable value in sql macro except NUMBER types. so to eliminate the problem above (column = nvl(paramater, column)) we can at least do something by adding more number variables:

set autotrace traceonly;

set serveroutput on;

var table_name varchar2(128);
exec :table_name := 'EMPLOYEES';

create or replace function sf_test(p_owner varchar2, p_owner_null number,
                                   p_object_name varchar2,  p_object_name_null number,
                                   p_object_id number,  p_object_id_null number,
                                   p_object_type varchar2,  p_object_type_null number) return varchar2 sql_macro as
  v_sql varchar2(4000) :=  'select * from tmp ';
  v_conditions varchar2(4000);
begin
  if p_owner_null = 0 then
    v_conditions := v_conditions || ' owner = sf_test.p_owner and ';
  end if;

  if p_object_name_null = 0 then
    v_conditions := v_conditions || ' object_name = sf_test.p_object_name and ';
  end if;

  if p_object_id_null = 0 then
    v_conditions := v_conditions || ' object_id = sf_test.p_object_id and ';
  end if;

  if p_object_type_null = 0 then
    v_conditions := v_conditions || ' object_type = sf_test.p_object_type and ';
  end if;
  
  if v_conditions is not null then
    v_conditions := ' where ' || v_conditions ;
  end if;
  
  v_conditions := substr(v_conditions, 1, length(v_conditions)-4); --just remove last " and "
  
  --dbms_output.put_line(v_Sql || v_conditions);
    
  return v_Sql ||v_conditions ;  
end;
/

select * from sf_Test(null,1, :table_name,0, null,1,null,1);

Execution Plan
----------------------------------------------------------
Plan hash value: 194862134

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |   133 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP    |     1 |   133 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I_TMP1 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"=:TABLE_NAME)

set autotrace traceonly;

set serveroutput on;

var table_name varchar2(128);

exec :table_name := 'EMPLOYEES';

create or replace function sf_test(p_owner varchar2, p_owner_null number,

p_object_name varchar2, p_object_name_null number,

p_object_id number, p_object_id_null number,

p_object_type varchar2, p_object_type_null number) return varchar2 sql_macro as

v_sql varchar2(4000) := 'select * from tmp ';

v_conditions varchar2(4000);

begin

if p_owner_null = 0 then

v_conditions := v_conditions || ' owner = sf_test.p_owner and ';

end if;

if p_object_name_null = 0 then

v_conditions := v_conditions || ' object_name = sf_test.p_object_name and ';

end if;

if p_object_id_null = 0 then

v_conditions := v_conditions || ' object_id = sf_test.p_object_id and ';

end if;

if p_object_type_null = 0 then

v_conditions := v_conditions || ' object_type = sf_test.p_object_type and ';

end if;

if v_conditions is not null then

v_conditions := ' where ' || v_conditions ;

end if;

v_conditions := substr(v_conditions, 1, length(v_conditions)-4); --just remove last " and "

--dbms_output.put_line(v_Sql || v_conditions);

return v_Sql ||v_conditions ;

end;

select * from sf_Test(null,1, :table_name,0, null,1,null,1);

Execution Plan

----------------------------------------------------------

Plan hash value: 194862134

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 133 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TMP | 1 | 133 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_TMP1 | 1 | | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_NAME"=:TABLE_NAME)

we can shape our sql in sql macro. as I said since I can only read NUMBER variables, I added parameters that indicates whether the real parameter is null or not and based on this information I constracted my sql statements.

What is “odd” about this?

when I see the first examples, I thought to create condition list using the parameter values. if a parameter is not null then add “column = p_parameter1” to query but something is different in this environment. even if we are writing a “function” and can use plsql commands like “if then else” or for loop, we can not read parameter values (even more oddly) except NUMBERS.

set autotrace off;
set serveroutput on;

create or replace function sf_test_output(p_param number) return varchar2 sql_macro as
    begin
      dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
      return 'select sf_test_output.p_param from dual';
    end;
    /


select * from sf_test_output(10);

   P_PARAM
----------
        10

1 row selected.

parameter p_param is 10

set autotrace off;

set serveroutput on;

create or replace function sf_test_output(p_param number) return varchar2 sql_macro as

begin

dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);

return 'select sf_test_output.p_param from dual';

end;

select * from sf_test_output(10);

P_PARAM

----------

1 row selected.

parameter p_param is 10

everything is normal here, I am showing the value using dbms_output and also my select is fetching parameter value from dual and both okay. query returned 1 row with value 10 and output is showing correct value.

Let’s do it with a varchar2:

create or replace function sf_test_output(p_param varchar2) return varchar2 sql_macro as
        begin
          dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);
          return 'select sf_test_output.p_param from dual';
        end;
        /

Function created.


select * from sf_test_output('ABC');

P_P
---
ABC

1 row selected.

parameter p_param is

create or replace function sf_test_output(p_param varchar2) return varchar2 sql_macro as

begin

dbms_output.put_line('parameter p_param is ' || sf_test_output.p_param);

return 'select sf_test_output.p_param from dual';

end;

Function created.

select * from sf_test_output('ABC');

P_P

---

ABC

1 row selected.

parameter p_param is

as you see, when parameter type is varchar2, then I can not read it inside the sql macro! my output is null (parameter p_param is) but query returned the value of parameter. same thing happens for date type too. So I can not use “if p_param = ‘ABC’ then” condition because p_param is null inside the sql macro but I can use “if p_param = 1 then” if p_param is a number.

I am running these codes on version 19.6 but also run them livesql.oracle.com which is 19.8 and same result.

I don’t know why it is acting like this maybe a bug maybe a feature 😀 I told that this is run by sql not plsql but I think this is not entirely true. some plsql engine is involved here but parameter values are not passed to plsql engine except numbers. of course this is just a guess I am not sure about it but I would like to use parameter values inside the sql macro.

wish you all healthy, corona free days.

What is “odd” about this?

Leave a Reply Cancel reply