One of the most mentioned use cases for table SQL macros is the implementation, which works like a parameterized view. My first post on this is from 2020 and it ends with a thought that we have many advantages here but no real view at all. And indeed, I have heard this argument several times since then. Views are schema objects that we have been familiar with for decades, they are very easy to develop and deploy, and their query code is easily accessible via data dictionary. On the other hand, maintaining query code within SQL macros can be a bit more difficult and understandably not to everyone’s preference. But what if you could have a cake and eat it too? Here is how…
Why might someone want parameters for a view?
In my view, the most common situations calling for parameter in views are as follows:
- Without the parameter(s) the view will take a significant amount of time and database resources and produce way too much data that nobody needs. Introducing a mandatory parameter will help you to document the intended use of the view. Without that you cannot force anyone to always use the view with a WHERE condition only
- Even being queried with a WHERE condition some views don’t allow predicate pushing and early filtering
In the second case you have to be very cautious and first check if applying the parameter outside and inside of the view is still semantically equivalent and doesn’t produce wrong results. For example, filtering before and after applying the analytical functions are two totally different queries.
For now, let’s take a very simple example based on Sales History (SH) schema:
CREATE OR REPLACE VIEW v_fct_sales AS
SELECT s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold
FROM sh.sales s
GROUP BY s.time_id, s.prod_id, s.cust_id ;
SELECT v.*
FROM v_fct_sales v
WHERE v.time_id = DATE '2019-01-07';
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 567 |00:00:00.01 |
| 1 | HASH GROUP BY | | 1 | 634 | 567 |00:00:00.01 |
| 2 | PARTITION RANGE SINGLE | | 1 | 634 | 634 |00:00:00.01 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 1 | 634 | 634 |00:00:00.01 |
| 4 | BITMAP CONVERSION TO ROWIDS | | 1 | | 634 |00:00:00.01 |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | | 1 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------------
5 - access("S"."TIME_ID"=TO_DATE(' 2019-01-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Listing 1: WHERE condition is pushed into the view’s query
Querying the view with the filter on time_id is efficient, reading and aggregating just 634 rows, because the filter is applied early.
Now, just to illustrate, I will introduce the pseudo column ROWNUM in this view, which will make the early application of the filter not possible:
CREATE OR REPLACE VIEW v_fct_sales_complex AS
SELECT s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold
FROM sh.sales s
WHERE ROWNUM >= 1
GROUP BY s.time_id, s.prod_id, s.cust_id ;
SELECT v.*
FROM v_fct_sales_complex v
WHERE v.time_id = DATE '2019-01-07';
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 567 |00:00:02.67 |
|* 1 | VIEW | V_FCT_SALES_COMPLEX | 1 | 918K| 567 |00:00:02.67 |
| 2 | HASH GROUP BY | | 1 | 918K| 700K|00:00:02.30 |
| 3 | COUNT | | 1 | | 918K|00:00:01.15 |
|* 4 | FILTER | | 1 | | 918K|00:00:01.01 |
| 5 | PARTITION RANGE ALL| | 1 | 918K| 918K|00:00:00.83 |
| 6 | TABLE ACCESS FULL | SALES | 15 | 918K| 918K|00:00:00.87 |
-----------------------------------------------------------------------------------------------
1 - filter("V"."TIME_ID"=TO_DATE(' 2019-01-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Listing 2: ROWNUM makes predicate pushing impossible
We are still using the same WHERE condition in the query, but this time we are reading the whole table SALES first, aggregating over the 918K rows, before the filter is applied.
How to address the problem replacing the view with a SQL macro?
It is pretty easy to introduce a table SQL macro accepting the needed parameter, then take the query of the view and put it into this macro, referencing the parameter at the corresponding position in that query, as shown in the Listing 3
CREATE OR REPLACE FUNCTION sqm_fct_sales_complex (p_time_id IN DATE)
RETURN CLOB SQL_MACRO(TABLE)
IS
BEGIN
RETURN q'[SELECT s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold
FROM sh.sales s
WHERE ROWNUM >= 1 AND s.time_id = p_time_id
GROUP BY s.time_id, s.prod_id, s.cust_id ]';
END;
/
SELECT *
FROM sqm_fct_sales_complex (DATE '2019-01-07');
-- After SQL macro expansion the query looks like following and leads to a good execution plan again
SELECT "A1"."TIME_ID" "TIME_ID","A1"."PROD_ID" "PROD_ID","A1"."CUST_ID" "CUST_ID","A1"."SUM_SOLD" "SUM_SOLD"
FROM (SELECT "A2"."TIME_ID" "TIME_ID","A2"."PROD_ID" "PROD_ID","A2"."CUST_ID" "CUST_ID","A2"."SUM_SOLD" "SUM_SOLD"
FROM (SELECT "A3"."TIME_ID" "TIME_ID","A3"."PROD_ID" "PROD_ID","A3"."CUST_ID" "CUST_ID",
SUM("A3"."AMOUNT_SOLD") "SUM_SOLD"
FROM "SH"."SALES" "A3"
WHERE ROWNUM>=1 AND "A3"."TIME_ID"=TO_DATE(' 2019-01-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
GROUP BY "A3"."TIME_ID","A3"."PROD_ID","A3"."CUST_ID")
"A2") "A1"
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 |
| 1 | VIEW | | 1 | 634 | 50 |00:00:00.01 |
| 2 | HASH GROUP BY | | 1 | 634 | 50 |00:00:00.01 |
| 3 | COUNT | | 1 | | 634 |00:00:00.01 |
|* 4 | FILTER | | 1 | | 634 |00:00:00.01 |
| 5 | PARTITION RANGE SINGLE | | 1 | 634 | 634 |00:00:00.01 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 1 | 634 | 634 |00:00:00.01 |
| 7 | BITMAP CONVERSION TO ROWIDS | | 1 | | 634 |00:00:00.01 |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | | 1 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------------
8 - access("S"."TIME_ID"=TO_DATE(' 2019-01-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Listing 3: The query text with a parameter is now inside a SQL macro
We are done. The filter condition can be applied early and we have a good execution plan again. The view is more or less useless now and you can drop it, unless you plan to use it further without parameter. If changes should be made to the query logic, you now have to do it changing the SQL macro instead of doing CREATE OR REPLACE VIEW .. AS SELECT… Or even worse – if you want to keep the view then you will have to do both…
How to address the problem keeping both the view and the SQL macro?
Let’s recreate the view using the query with the parameter. Well, we can’t do it, right? The usage of a parameter name is illegal in a query text. So we’ll just comment out this line, but using a special kind of comments that we can easily recognize them later on, e.g. using /*+–PARAM and PARAM–*/ This will be the task of the SQL macro: read the query for the view from data dictionary, find and remove the comment marks, done!
CREATE OR REPLACE VIEW v_fct_sales_complex AS
SELECT s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold
FROM sh.sales s
WHERE ROWNUM >= 1
/*--PARAM and s.time_id = p_time_id PARAM--*/
GROUP BY s.time_id, s.prod_id, s.cust_id ;
CREATE OR REPLACE FUNCTION sqm_fct_sales_complex (p_time_id IN DATE)
RETURN CLOB SQL_MACRO(TABLE)
IS
v_query LONG;
BEGIN
SELECT text
INTO v_query
FROM user_views
WHERE view_name = 'V_FCT_SALES_COMPLEX';
RETURN regexp_replace(v_query, '(\/\*--PARAM)(.*?p_time_id .*?)(PARAM--\*\/)', '\2',1,1,'i') );
END;
/
SELECT *
FROM sqm_fct_sales_complex (DATE '2019-01-07');
Listing 4: The SQL macro doesn’t contain the former view code anymore
With this approach you can still keep the view and maintain the query there. If you need, you can still use the view as is. You can introduce multiple parameter placeholder and handle them from different or from the same SQL macro.
The biggest drawback I see so far is that we are using comments for something that can have functional impact. But don’t we do the same with optimizer hints? Someone who is not aware of the concept can manipulate these comments or even remove them. The SQL macro function as shown in Listing 4 will still work without any error, just ignoring your parameter. This can lead to bugs that are hard to find. To handle this case you can make the SQL macro function check for the presence of the parameter in the view text first, throwing an error otherwise. But anyway, your are testing the changes thoroughly, right?
Don’t stop reading here!
There are a few very important points to bear in mind.
Parsing
First of all, what happens to the queries using the new SQL macro, if someone changes the view definition? The correct answer – nothing happens to them, unless they are hard parsed again. Until then they are not aware of the changed view! Of course, this is not something that you will expect after changing the view. In order to make the view changes have an immediate effect on all queries using SQL macro, you will need one more step while deploying them. Iudith Mentzel has proposed a very nice trick – just invalidate the SQL macro and this will invalidate all SQL cursors using it. Next time you call these queries they will be hard parsed again.
-- Change the View
CREATE OR REPLACE VIEW v_fct_sales_complex …
-- Invalidate SQL Macros
BEGIN
SELECT object_id INTO l_object_id FROM all_objects
WHERE (owner, object_name)=((user,'SQM_FCT_SALES_COMPLEX'));
dbms_utility.invalidate(l_object_id);
END;
Listing 5: Invalidation of a SQL macro after the view definition is changed
Privileges
To process the view code we have to search for it in the data dictionary first. Do we need some privileges to access it from PL/SQL code, you may ask? If you want to query ALL_VIEWS or DBA_VIEWS, you will need the access rights granted directly, because your possible roles are inactive in PL/SQL anyway. Placing the view and the SQL macro in the same schema can simplify things here – we can just query USER_VIEWS without any additional privileges.
Another good question is, whose views will I see in USER_VIEWS: from the schema that owns the SQL macro function or from the caller’s schema? In other words, does SQL macro run as invoker or definer PL/SQL unit? As clarified in one of the earlier posts, SQL macro function is always executed with definer privileges.
Parameter Substitution
Make sure you pay attention to how we are doing the parameter substitution. Well, actually we don’t do any substitution ourselves. Instead, we just remove the comment markers, leaving the condition with the parameter in the returned query, like and s.time_id = p_time_id. Of course, the parameter name used in the view’s query must match with the parameter name of the SQL macro function. The actual replacement of the parameter names with their values is done by the database automatically and it happens after then SQL macro expansion during parsing.
Conclusion
This post was inspired by feedback from some users who found the use of SQL macros for parameterizable views promising on the one hand, but on the other hand did not want to give up their views. I have to admit, the idea is not very mature yet. I haven’t used it yet and I haven’t thought about it thoroughly for days and weeks. So any feedback is very much appreciated.
Hello Andrej,
I am always so glad to follow after your excellent posts 🙂
Along my “Oracle history”, I have traversed two methods of “parametrizing views”.
1. Early on, before Oracle8i, when we wanted to use parameters in a view, we used a parameter table,
having as primary key a SESSION_ID column:.
Then, the view was defined as follows:
CREATE VIEW v AS
SELECT ...
FROM t1, t2 ..., param_table p
WHERE
p.session_id = USERENV('SESSIONID')
AND ...
Before using the view, a parameter record was inserted into the param_table:
INSERT INTO param_table (session_id, par1, par2 ...) VALUES ( USERENV('SESSIONID'), ... );Thus, each session used its own parameters.
2. Starting with Oracle 8i, we could use context variables and SYS_CONTEXT calls in the view definition,
with the requirement that before using the view, the context variables had to be set with the corresponding
parameter values.
And, again, here also each session uses its own parameters.
Each of these methods requires an additional step for “setting up” the parameters.
But, I think that the reason for which the term “parametrized views” was described as a new feature just together
with the appearance of the SQL MACRO-s is probably the fact that the syntax of “calling the SQL MACRO”
with a list of parameter values specified in paranthesis does resemble more closely the call of a subprogram with parameters.
Essentially, however, their functionality is the same, except for the issue that in case of the SQL MACRO-s,
we have a new parse with each new set of parameter values used, which may be for better or for worse performance-wise.
Looking backwards in time, and comparing the old times with the explosion of new features in the recent versions,
I however always felt satisfied for having found such solutions even with a “smaller tool bag”.
Cheers & Best Regards,
Iudith Mentzel
Hello Iudith,
Thank you for your feedback.
Yes, I have used the SYS_CONTEXT workaround too. And one thing that bothered me a bit – besides the need to set the context first – is that I have no control over whether my parameters are used as literals or binds. With SYS_CONTEXT they were always as bind variables. And then you have to rely on bind peeking working, the cursor being “bind-aware”, etc.
I have a “data warehouse-driven” mindset. And in this context, the use of literals is often more advantageous. That’s why I really liked the fact that with SQL macros you have control yourself: if you pass the parameters as binds, they also become binds in the resulting SQL. This also gives you control over how often hard parsing takes place, as Stew also mentioned in his answer.
Thank you again and Best regards,
Andrej
Hi Andrej,
First of all, good luck with your presentation!
After some thought, I suggest an adjustment to your approach:
1) Include at the beginning of the view a WITH clause that contains all the “parameters” to be used later.
2) In the view, put literals in the WITH clause for testing purposes and so the view will be valid.
3) In a generic SQL table macro, make the view a DBMS_TF_.TABLE_T parameter. This way, if the view changes the *cursor* will be invalidated, not the entire macro.
4) remove the hard-coded WITH clause and put the “parameters” in a WITH clause that precedes the SELECT of the macro.
CREATE OR REPLACE VIEW v_fct_sales_complex AS
with p_parms as (Select sysdate time_id from dual)
SELECT s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold
FROM sh.sales s
WHERE ROWNUM >= 1 and s.time_id = (select time_id from p_parms)
GROUP BY s.time_id, s.prod_id, s.cust_id;
create or replace function parameterized_view(
p_view dbms_tf.table_t,
p_parms dbms_tf.table_t
)
return clob sql_macro is
l_view_text long;
begin
select text into l_view_text from user_views
where view_name = trim('"' from p_view.table_name);
l_view_text :=
regexp_replace(
regexp_replace(l_view_text, 'with\s*p_parms[^)]*\)\s*', '', 1, 0, 'i'),
'^,', 'with '
);
--dbms_output.put_line(l_view_text);
return l_view_text;
end parameterized_view;
/
with parms as (select date '1999-10-03' time_id from dual)
select * from parameterized_view(v_fct_sales_complex, Parms);
Best regards,
Stew
P.S. Our good friend Iudith says “we have a new parse with each new set of parameter values used”. That is true if the parameter values are literals, but if they are bind variables we can avoid the new parse.
Hello Stew,
Thank you for your wishes! And of course for your feedback!
Since you are suggesting an adjustment to my approach, may I assume that you are generally in favor of keeping the view (with a SQL macro addition) instead of replacing it completely with SQL macro?
As for your suggestion, I think it makes a lot of sense, especially for the views with mandatory parameters that have default values. And also if a generic SQL macro is preferred. I rather thought of the view and SQM as a specific pair working together. The view could generally be used without parameter, with all or just some of them.
With your approach, if I’ve got it right, you have to add something like
s.time_id = (select time_id from p_parms) or (select time_id from p_parms) is nullif we want time_id to be optional? But this leads to really crazy execution plans.
If I have a view-specific macro, then it is the same whether only the cursor is invalidated or the macro, right? And I do not benefit additionally from passing the view as DBMS_TF.Table_T. So far I have not been a fan of using DBMS_TF.Table_T.table_name in SQM. For named subqueries the field will be empty. But in this use case it is of course safe.
The other thing that gives me a headache is the way we call this construct. A developer can still write a query with SQM and parameters and possibly a WITH clause, but the BI-tools out there that generate their queries will never do that.
I have even tested using SQL Transaltion Framework to make it possible to call this way:
select *
from v_fct_sales_complex
where p_time_id = date '1999-10-03'
and it will translate it to a SQL macro call. But that’s just a gimmick, I think, nobody will use it seriously.
Best regards,
Andrej