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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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")) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.