{"id":1281,"date":"2024-04-11T08:17:44","date_gmt":"2024-04-11T06:17:44","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1281"},"modified":"2024-04-13T18:53:24","modified_gmt":"2024-04-13T16:53:24","slug":"parameterized-views-with-sql-macros-part-2","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/parameterized-views-with-sql-macros-part-2\/","title":{"rendered":"Parameterized Views with SQL Macros (Part 2)"},"content":{"rendered":"\n<p>One of the most mentioned use cases for table SQL macros is the implementation, which works like a parameterized view. My <a rel=\"noreferrer noopener\" href=\"https:\/\/blog.sqlora.com\/en\/parameterized-views-in-oracle-no-problem-with-sql-macros\/\" target=\"_blank\">first post<\/a> 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&#8217;s preference. But what if you could have a cake and eat it too? Here is how&#8230;<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Why might someone want parameters for a view?<\/h2>\n\n\n\n<p>In my view, the most common situations calling for parameter in views are as follows: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>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<\/li>\n\n\n\n<li>Even being queried with a WHERE condition some views don&#8217;t allow predicate pushing and early filtering <\/li>\n<\/ul>\n\n\n\n<p>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&#8217;t produce wrong results. For example, filtering before and after applying the analytical functions are two totally different queries.<\/p>\n\n\n\n<p>For now, let&#8217;s take a very simple example based on Sales History (SH) schema: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE OR REPLACE VIEW v_fct_sales AS\nSELECT  s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold\nFROM    sh.sales s \nGROUP BY  s.time_id, s.prod_id, s.cust_id ;\n\nSELECT v.*\nFROM   v_fct_sales v\nWHERE  v.time_id = DATE &#039;2019-01-07&#039;;\n\n--------------------------------------------------------------------------------------------------------------\n| Id  | Operation                                   | Name           | Starts | E-Rows | A-Rows |   A-Time   |\n--------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                            |                |      1 |        |    567 |00:00:00.01 |\n|   1 |  HASH GROUP BY                              |                |      1 |    634 |    567 |00:00:00.01 |\n|   2 |   PARTITION RANGE SINGLE                    |                |      1 |    634 |    634 |00:00:00.01 |\n|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      1 |    634 |    634 |00:00:00.01 |\n|   4 |     BITMAP CONVERSION TO ROWIDS             |                |      1 |        |    634 |00:00:00.01 |\n|*  5 |      BITMAP INDEX SINGLE VALUE              | SALES_TIME_BIX |      1 |        |      1 |00:00:00.01 |\n--------------------------------------------------------------------------------------------------------------\n\n5 - access(&quot;S&quot;.&quot;TIME_ID&quot;=TO_DATE(&#039; 2019-01-07 00:00:00&#039;, &#039;syyyy-mm-dd hh24:mi:ss&#039;))\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 1: WHERE condition is pushed into the view&#8217;s query<\/em><\/strong><\/p>\n\n\n\n<p>Querying the view with the filter on time_id is efficient, reading and aggregating just 634 rows, because the  filter is applied early. <\/p>\n\n\n\n<p>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:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE OR REPLACE VIEW v_fct_sales_complex AS\nSELECT  s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold\nFROM    sh.sales s \nWHERE   ROWNUM &gt;= 1\nGROUP BY  s.time_id, s.prod_id, s.cust_id ;\n\nSELECT v.*\nFROM   v_fct_sales_complex v\nWHERE  v.time_id = DATE &#039;2019-01-07&#039;;\n\n-----------------------------------------------------------------------------------------------\n| Id  | Operation               | Name                | Starts | E-Rows | A-Rows |   A-Time   |\n-----------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT        |                     |      1 |        |    567 |00:00:02.67 |\n|*  1 |  VIEW                   | V_FCT_SALES_COMPLEX |      1 |    918K|    567 |00:00:02.67 |\n|   2 |   HASH GROUP BY         |                     |      1 |    918K|    700K|00:00:02.30 |\n|   3 |    COUNT                |                     |      1 |        |    918K|00:00:01.15 |\n|*  4 |     FILTER              |                     |      1 |        |    918K|00:00:01.01 |\n|   5 |      PARTITION RANGE ALL|                     |      1 |    918K|    918K|00:00:00.83 |\n|   6 |       TABLE ACCESS FULL | SALES               |     15 |    918K|    918K|00:00:00.87 |\n----------------------------------------------------------------------------------------------- \n1 - filter(&quot;V&quot;.&quot;TIME_ID&quot;=TO_DATE(&#039; 2019-01-07 00:00:00&#039;, &#039;syyyy-mm-dd hh24:mi:ss&#039;))\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 2: ROWNUM makes predicate pushing impossible<\/em><\/strong><\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to address the problem replacing the view with a SQL macro?<\/h2>\n\n\n\n<p>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<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE OR REPLACE FUNCTION sqm_fct_sales_complex (p_time_id IN DATE)\nRETURN CLOB SQL_MACRO(TABLE)\nIS \nBEGIN\n  RETURN q&#039;&#x5B;SELECT  s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold\n\t   FROM    sh.sales s \n\t   WHERE   ROWNUM &gt;= 1 AND s.time_id = p_time_id\n\t   GROUP BY  s.time_id, s.prod_id, s.cust_id ]&#039;;\nEND;\n\/\n\nSELECT *\nFROM   sqm_fct_sales_complex (DATE &#039;2019-01-07&#039;); \n\n-- After SQL macro expansion the query looks like following and leads to a good execution plan again\n\nSELECT &quot;A1&quot;.&quot;TIME_ID&quot; &quot;TIME_ID&quot;,&quot;A1&quot;.&quot;PROD_ID&quot; &quot;PROD_ID&quot;,&quot;A1&quot;.&quot;CUST_ID&quot; &quot;CUST_ID&quot;,&quot;A1&quot;.&quot;SUM_SOLD&quot; &quot;SUM_SOLD&quot; \nFROM  (SELECT &quot;A2&quot;.&quot;TIME_ID&quot; &quot;TIME_ID&quot;,&quot;A2&quot;.&quot;PROD_ID&quot; &quot;PROD_ID&quot;,&quot;A2&quot;.&quot;CUST_ID&quot; &quot;CUST_ID&quot;,&quot;A2&quot;.&quot;SUM_SOLD&quot; &quot;SUM_SOLD&quot; \n    FROM  (SELECT &quot;A3&quot;.&quot;TIME_ID&quot; &quot;TIME_ID&quot;,&quot;A3&quot;.&quot;PROD_ID&quot; &quot;PROD_ID&quot;,&quot;A3&quot;.&quot;CUST_ID&quot; &quot;CUST_ID&quot;,\n\t\t  SUM(&quot;A3&quot;.&quot;AMOUNT_SOLD&quot;) &quot;SUM_SOLD&quot; \n    \t FROM &quot;SH&quot;.&quot;SALES&quot; &quot;A3&quot; \n\t WHERE ROWNUM&gt;=1 AND &quot;A3&quot;.&quot;TIME_ID&quot;=TO_DATE(&#039; 2019-01-07 00:00:00&#039;, &#039;syyyy-mm-dd hh24:mi:ss&#039;) \n           GROUP BY &quot;A3&quot;.&quot;TIME_ID&quot;,&quot;A3&quot;.&quot;PROD_ID&quot;,&quot;A3&quot;.&quot;CUST_ID&quot;) \n     &quot;A2&quot;) &quot;A1&quot;\n\n-----------------------------------------------------------------------------------------------------------------\n| Id  | Operation                                      | Name           | Starts | E-Rows | A-Rows |   A-Time   |\n-----------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                               |                |      1 |        |     50 |00:00:00.01 |\n|   1 |  VIEW                                          |                |      1 |    634 |     50 |00:00:00.01 |\n|   2 |   HASH GROUP BY                                |                |      1 |    634 |     50 |00:00:00.01 |\n|   3 |    COUNT                                       |                |      1 |        |    634 |00:00:00.01 |\n|*  4 |     FILTER                                     |                |      1 |        |    634 |00:00:00.01 |\n|   5 |      PARTITION RANGE SINGLE                    |                |      1 |    634 |    634 |00:00:00.01 |\n|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      1 |    634 |    634 |00:00:00.01 |\n|   7 |        BITMAP CONVERSION TO ROWIDS             |                |      1 |        |    634 |00:00:00.01 |\n|*  8 |         BITMAP INDEX SINGLE VALUE              | SALES_TIME_BIX |      1 |        |      1 |00:00:00.01 |\n-----------------------------------------------------------------------------------------------------------------\n8 - access(&quot;S&quot;.&quot;TIME_ID&quot;=TO_DATE(&#039; 2019-01-07 00:00:00&#039;, &#039;syyyy-mm-dd hh24:mi:ss&#039;))\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 3: The query text with a parameter is now inside a SQL macro<\/em><\/strong><\/p>\n\n\n\n<p>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&#8230; Or even worse &#8211; if you want to keep the view then you will have to do both&#8230;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to address the problem keeping both the view and the SQL macro?<\/h2>\n\n\n\n<p>Let&#8217;s recreate the view using the query with the parameter. Well, we can&#8217;t do it, right? The usage of a parameter name is illegal in a query text. So we&#8217;ll just comment out this line, but using a special kind of comments that we can easily recognize them later on, e.g. using \/*+&#8211;PARAM  and PARAM&#8211;*\/ 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! <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE OR REPLACE VIEW v_fct_sales_complex AS\nSELECT  s.time_id, s.prod_id, s.cust_id, SUM(s.amount_sold) sum_sold\nFROM    sh.sales s \nWHERE   ROWNUM &gt;= 1\n\/*--PARAM and s.time_id = p_time_id PARAM--*\/\nGROUP BY  s.time_id, s.prod_id, s.cust_id ;\n\nCREATE OR REPLACE FUNCTION sqm_fct_sales_complex (p_time_id IN DATE)\nRETURN CLOB SQL_MACRO(TABLE)\nIS \nv_query LONG;\nBEGIN\n  SELECT text \n  INTO   v_query \n  FROM   user_views \n  WHERE  view_name = &#039;V_FCT_SALES_COMPLEX&#039;;\n  \n  RETURN regexp_replace(v_query, &#039;(\\\/\\*--PARAM)(.*?p_time_id .*?)(PARAM--\\*\\\/)&#039;, &#039;\\2&#039;,1,1,&#039;i&#039;) );\n\nEND;\n\/ \nSELECT *\nFROM   sqm_fct_sales_complex (DATE &#039;2019-01-07&#039;); \n\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 4: The SQL macro doesn&#8217;t contain the former view code anymore<\/em><\/strong><\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>The biggest drawback I see so far is that we are using <strong>comments<\/strong> for something that can have functional impact. But don&#8217;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?<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Don&#8217;t stop reading here! <\/strong><\/h2>\n\n\n\n<p>There are a few very important points to bear in mind. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Parsing <\/h3>\n\n\n\n<p>First of all, what happens to the queries using the new SQL macro, if someone changes the view definition? The correct answer &#8211; 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. <a rel=\"noreferrer noopener\" href=\"https:\/\/twitter.com\/mentzel_iudith\" target=\"_blank\">Iudith Mentzel<\/a> has proposed a very nice trick &#8211; 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.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- Change the View\nCREATE OR REPLACE VIEW v_fct_sales_complex \u2026\n\n-- Invalidate SQL Macros\nBEGIN\n  SELECT object_id INTO l_object_id FROM all_objects \n  WHERE (owner, object_name)=((user,&#039;SQM_FCT_SALES_COMPLEX&#039;));\n\n  dbms_utility.invalidate(l_object_id);\nEND;\n\n<\/pre><\/div>\n\n\n<p><strong><em>Listing 5: Invalidation of a SQL macro after the view definition is changed<\/em><\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Privileges<\/h3>\n\n\n\n<p>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 &#8211; we can just query USER_VIEWS without any additional privileges. <\/p>\n\n\n\n<p>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&#8217;s schema? In other words, does SQL macro run as invoker or definer PL\/SQL unit? As clarified in <a href=\"https:\/\/blog.sqlora.com\/en\/sql-macros-and-invoker-rights\/\" target=\"_blank\" rel=\"noreferrer noopener\">one of the earlier posts<\/a>, SQL macro function is always executed with definer privileges.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Parameter Substitution<\/h3>\n\n\n\n<p>Make sure you pay attention to how we are doing the parameter substitution. Well, actually we don&#8217;t do any substitution ourselves. Instead, we just remove the comment markers, leaving the condition with the parameter in the returned query, like <code><strong>and s.time_id = p_time_id.<\/strong><\/code>  Of course, the parameter name used in the view&#8217;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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>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&#8217;t used it yet and I haven&#8217;t thought about it thoroughly for days and weeks. So any feedback is very much appreciated. <\/p>\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-2925154690547867\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-client=\"ca-pub-2925154690547867\" \ndata-ad-slot=\"2727403138\" \ndata-ad-layout=\"in-article\"\ndata-ad-format=\"fluid\"><\/ins>\n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n\n\n<div class=\"crp-list-container\"><h3 class=\"crp-list-title\">Related Posts<\/h3><ul class=\"crp-list\"><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/parameterized-views-in-oracle-no-problem-with-sql-macros\/\" target=\"_blank\">Parameterized Views in Oracle? No problem! With SQL macros!<\/a><\/div><\/li><li class=\"crp-list-item crp-list-item-image-none\"><div class=\"crp-list-item-title\"><a href=\"https:\/\/blog.sqlora.com\/en\/sql-macros-and-invoker-rights\/\" target=\"_blank\">SQL-Macros and Invoker Rights<\/a><\/div><\/li><\/ul><\/div>","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1245,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,100],"tags":[106,103],"class_list":["post-1281","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","category-sql","category-sql-macros","tag-parameterized-view","tag-sql-macros"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=1281"}],"version-history":[{"count":28,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1281\/revisions"}],"predecessor-version":[{"id":2146,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/1281\/revisions\/2146"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/1245"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=1281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=1281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=1281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}