{"id":9417,"date":"2019-12-13T18:24:17","date_gmt":"2019-12-13T17:24:17","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=9417"},"modified":"2023-11-08T10:55:47","modified_gmt":"2023-11-08T09:55:47","slug":"constants-vs-parameterless-functions","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/","title":{"rendered":"Constants vs. Parameterless Functions"},"content":{"rendered":"\n

Do you use parameterless PL\/SQL functions in your queries? Did you know that this may cause performance issues? In this blog post, I explain why parameterless functions can be the reason for bad execution plans in any Oracle Database.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

I recently had to analyze this problem in a production system and thought it was worth sharing. On the one hand, because we did not find a satisfactory solution and on the other hand because this could change in the future when we start discussing it.<\/p>\n\n\n\n

For this blog post, I used an Oracle Database 19c Enterprise Edition, version 19.5.0.0.0 in a Docker environment. However, you can run the scripts in any edition of an Oracle Database 12c Release 1 or later to reproduce the results.<\/p>\n\n\n\n

1. Data Setup<\/h2>\n\n\n\n

We create a user demo<\/code>  with ALTER SESSION<\/code> and SELECT ANY DICTIONARY<\/code> privileges as follows:<\/p>\n\n\n\n

Create user demo<\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>USER<\/span> <\/span>demo<\/span> IDENTIFIED <\/span>BY<\/span> demo  <\/span><\/span>\nDEFAULT<\/span> TABLESPACE users<\/span><\/span>\nTEMPORARY TABLESPACE temp<\/span><\/span>\nQUOTA <\/span>UNLIMITED<\/span> <\/span>ON<\/span> users;<\/span><\/span>\n<\/span>\nGRANT<\/span> <\/span>connect<\/span>, <\/span>resource<\/span> <\/span>TO<\/span> demo;<\/span><\/span>\nGRANT<\/span> <\/span>alter<\/span> <\/span>session<\/span> <\/span>TO<\/span> demo;<\/span><\/span>\nGRANT<\/span> <\/span>select<\/span> any dictionary <\/span>TO<\/span> demo;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Then as user demo<\/code> we create a table t<\/code> with an index t_ind_idx<\/code> on column ind<\/code><\/p>\n\n\n\n
Create table t<\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>TABLE<\/span> <\/span>t<\/span> (<\/span><\/span>\n   id   <\/span>INTEGER<\/span>       <\/span>GENERATED<\/span> <\/span>ALWAYS<\/span> <\/span>AS<\/span> <\/span>IDENTITY<\/span> <\/span>CONSTRAINT<\/span> t_pk <\/span>PRIMARY KEY<\/span>,<\/span><\/span>\n   ind  <\/span>INTEGER<\/span>       <\/span>NOT NULL<\/span> <\/span>CONSTRAINT<\/span> ind_ck <\/span>CHECK<\/span> (ind <\/span>IN<\/span> (<\/span>0<\/span>, <\/span>1<\/span>)), <\/span><\/span>\n   <\/span>text<\/span> <\/span>VARCHAR2<\/span>(<\/span>100<\/span>) <\/span>NOT NULL<\/span><\/span>\n);<\/span><\/span>\nCREATE<\/span> <\/span>INDEX<\/span> <\/span>t_ind_idx<\/span> <\/span>ON<\/span> t (ind);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
and populated table t<\/code> with the following anonymous PL\/SQL block:<\/p>\n\n\n\n
Populate table t<\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span><\/span>\n   <\/span>dbms_random.<\/span>seed<\/span>(<\/span>0<\/span>);<\/span><\/span>\n   <\/span>INSERT<\/span> <\/span>INTO<\/span> t (ind, text)<\/span><\/span>\n   <\/span>SELECT<\/span> <\/span>CASE<\/span> <\/span><\/span>\n             <\/span>WHEN<\/span> <\/span>dbms_random.<\/span>value<\/span>(<\/span>0<\/span>, <\/span>999<\/span>) < <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n                <\/span>1<\/span> <\/span><\/span>\n             <\/span>ELSE<\/span> <\/span><\/span>\n                <\/span>0<\/span><\/span>\n          <\/span>END<\/span> <\/span>AS<\/span> ind,<\/span><\/span>\n          <\/span>dbms_random.<\/span>string<\/span>(<\/span>'p'<\/span>, <\/span>round<\/span>(<\/span>dbms_random.<\/span>value<\/span>(<\/span>5<\/span>, <\/span>100<\/span>),<\/span>0<\/span>)) <\/span>AS<\/span> text<\/span><\/span>\n     <\/span>FROM<\/span> <\/span>xmltable<\/span>(<\/span>'1 to 100000'<\/span>);<\/span><\/span>\n   <\/span>COMMIT<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The case expression leads to a skewed distribution of column ind<\/code>. Only around 0.1% of the rows have a value 1<\/code> as the following query shows:<\/p>\n\n\n\n
Distribution of column ind<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> ind, <\/span>count<\/span>(*) <\/span><\/span>\n  <\/span>FROM<\/span> t <\/span><\/span>\n <\/span>GROUP BY<\/span> ind;<\/span><\/span>\n<\/span>\n       IND   <\/span>COUNT<\/span>(*)<\/span><\/span>\n---------- ----------<\/span><\/span>\n         <\/span>1<\/span>        <\/span>101<\/span><\/span>\n         <\/span>0<\/span>      <\/span>99899<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Therefore we are gathering statistics for table t<\/code>  with a histogram for column ind<\/code>:<\/p>\n\n\n\n
Gather statistics<\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span><\/span>\n   <\/span>dbms_stats.<\/span>gather_table_stats<\/span>(<\/span><\/span>\n      ownname    => <\/span>user<\/span>, <\/span><\/span>\n      tabname    => <\/span>'T'<\/span>, <\/span><\/span>\n      method_opt => <\/span>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 2 IND'<\/span><\/span>\n   );<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now, we can check the histogram with the following query:<\/p>\n\n\n\n
Histogramm of column ind<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> endpoint_value, endpoint_number <\/span><\/span>\n  <\/span>FROM<\/span> user_histograms<\/span><\/span>\n <\/span>WHERE<\/span> table_name = <\/span>'T'<\/span> <\/span><\/span>\n   <\/span>AND<\/span> column_name = <\/span>'IND'<\/span>;<\/span><\/span>\n<\/span>\nENDPOINT_VALUE ENDPOINT_NUMBER<\/span><\/span>\n-------------- ---------------<\/span><\/span>\n             <\/span>0<\/span>           <\/span>99899<\/span><\/span>\n             <\/span>1<\/span>          <\/span>100000<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Two rows for the two values of column ind<\/code>. For value 0<\/code> we expect 99899 rows (endpoints) and for value 1<\/code>  we expect 101 rows (100000 – 99899 endpoints). This is 100 percent accurate.<\/p>\n\n\n\n
2. Constant Declaration<\/h2>\n\n\n\n
In the Trivadis PL\/SQL & SQL Guidelines<\/a> we recommend avoiding the use of literals in PL\/SQL code. Every time we see a literal in a PL\/SQL code we should consider using a constant instead. Often this makes sense because the name of the constant is more meaningful than the literal, making the code more readable and maintainable.<\/p>\n\n\n\n
Hence we create the following PL\/SQL package for our representation of boolean values in SQL:<\/p>\n\n\n\n
PL\/SQL package for boolean values<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>const_boolean<\/span> AUTHID DEFINER <\/span>IS<\/span><\/span>\n   co_true  <\/span>CONSTANT<\/span> <\/span>INTEGER<\/span> := <\/span>1<\/span>;<\/span><\/span>\n   co_false <\/span>CONSTANT<\/span> <\/span>INTEGER<\/span> := <\/span>0<\/span>;<\/span><\/span>\nEND<\/span> const_boolean;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now we can use these constants in our PL\/SQL code as follows:<\/p>\n\n\n\n
SQL using a constant (within PL\/SQL)<\/span><\/path><\/path><\/svg><\/span>
SET<\/span> SERVEROUTPUT <\/span>ON<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>FOR<\/span> r <\/span>IN<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>count<\/span>(*) <\/span>AS<\/span> open_count<\/span><\/span>\n        <\/span>FROM<\/span> t <\/span><\/span>\n       <\/span>WHERE<\/span> ind = const_boolean.co_true<\/span><\/span>\n   ) <\/span>LOOP<\/span><\/span>\n      <\/span>dbms_output.<\/span>put_line<\/span>(<\/span>'open: '<\/span> || r.open_count);<\/span><\/span>\n   <\/span>END LOOP<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nopen<\/span>: <\/span>101<\/span><\/span>\n<\/span>\nPL\/<\/span>SQL<\/span> procedure successfully completed.<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
When developing complex SQL statements I often run them standalone in an IDE until I’m satisfied with the result. But when we run this<\/p>\n\n\n\n
SQL using a constant (outside of PL\/SQL)<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>count<\/span>(*) <\/span>AS<\/span> open_count<\/span><\/span>\n  <\/span>FROM<\/span> t <\/span><\/span>\n <\/span>WHERE<\/span> ind = const_boolean.co_true;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
we get the following error message:<\/p>\n\n\n\n
Error when using a constant in SQL<\/span><\/path><\/path><\/svg><\/span>
Error starting at line : 1 in command -<\/span><\/span>\nSELECT count(*) AS open_count<\/span><\/span>\n  FROM t <\/span><\/span>\n WHERE ind = const_boolean.co_true<\/span><\/span>\nError at Command Line : 3 Column : 14<\/span><\/span>\nError report -<\/span><\/span>\nSQL Error: ORA-06553: PLS-221: 'CO_TRUE' is not a procedure or is undefined<\/span><\/span>\n06553. 00000 -  "PLS-%s: %s"<\/span><\/span>\n*Cause:    <\/span><\/span>\n*Action:<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
We have to change the constant const_boolean.co_true<\/code>  to a literal (1<\/code>), which is cumbersome and error-prone.<\/p>\n\n\n\n
3. Parameterless Functions for Constants<\/h2>\n\n\n\n
As a workaround, we can create a parameterless function for each constant. Like this:<\/p>\n\n\n\n
Parameterless function for each constant<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>const_boolean<\/span> AUTHID DEFINER <\/span>IS<\/span><\/span>\n   co_true  <\/span>CONSTANT<\/span> <\/span>INTEGER<\/span> := <\/span>1<\/span>;<\/span><\/span>\n   co_false <\/span>CONSTANT<\/span> <\/span>INTEGER<\/span> := <\/span>0<\/span>;<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>true<\/span># <\/span>RETURN<\/span> <\/span>INTEGER<\/span> <\/span>DETERMINISTIC<\/span>;<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>false<\/span># <\/span>RETURN<\/span> <\/span>INTEGER<\/span> <\/span>DETERMINISTIC<\/span>;<\/span><\/span>\nEND<\/span> const_boolean;<\/span><\/span>\n\/<\/span><\/span>\nCREATE OR REPLACE<\/span> <\/span>PACKAGE BODY<\/span> <\/span>const_boolean<\/span> <\/span>IS<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>true<\/span># <\/span>RETURN<\/span> <\/span>INTEGER<\/span> <\/span>DETERMINISTIC<\/span> <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> co_true;<\/span><\/span>\n   <\/span>END<\/span> <\/span>true<\/span>#;<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>false<\/span># <\/span>RETURN<\/span> <\/span>INTEGER<\/span> <\/span>DETERMINISTIC<\/span> <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n     <\/span>RETURN<\/span> co_false;<\/span><\/span>\n   <\/span>END<\/span> <\/span>false<\/span>#;<\/span><\/span>\nEND<\/span> const_boolean;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now we can use the function in PL\/SQL and SQL like this:<\/p>\n\n\n\n
SQL<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>count<\/span>(*) <\/span>AS<\/span> open_count<\/span><\/span>\n  <\/span>FROM<\/span> t <\/span><\/span>\n <\/span>WHERE<\/span> ind = const_boolean.true#;<\/span><\/span>\n<\/span>\nOPEN_COUNT<\/span><\/span>\n----------<\/span><\/span>\n       <\/span>101<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
So far so good.<\/p>\n\n\n\n
4. The Problem<\/h2>\n\n\n\n
The execution plan of the previous statement looks as follows:<\/p>\n\n\n\n
Bad execution plan<\/span><\/path><\/path><\/svg><\/span>
SQL_ID  bg67gqa8f48j8, child number 0<\/span><\/span>\n-------------------------------------<\/span><\/span>\nSELECT count(*) AS open_count  FROM t  WHERE ind = const_boolean.true#<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 3395265327<\/span><\/span>\n <\/span><\/span>\n-----------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n-----------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT      |           |       |       |    75 (100)|          |<\/span><\/span>\n|   1 |  SORT AGGREGATE       |           |     1 |     3 |            |          |<\/span><\/span>\n|*  2 |   INDEX FAST FULL SCAN| T_IND_IDX | 50000 |   146K|    75  (12)| 00:00:01 |<\/span><\/span>\n-----------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - filter("IND"="CONST_BOOLEAN"."TRUE#"())<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
When you look at line 12 you see that the optimizer estimates to process 50000 rows. That’s 50 percent of all rows. This is based on the number of distinct values for column ind<\/code> and the number of rows in the table t<\/code>. The optimizer gets this information from here:<\/p>\n\n\n\n
Table and column statistics<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> num_rows <\/span><\/span>\n  <\/span>FROM<\/span> user_tables <\/span><\/span>\n <\/span>WHERE<\/span> table_name = <\/span>'T'<\/span>;<\/span><\/span>\n<\/span>\n  NUM_ROWS<\/span><\/span>\n----------<\/span><\/span>\n    <\/span>100000<\/span><\/span>\n<\/span>\nSELECT<\/span> num_distinct <\/span><\/span>\n  <\/span>FROM<\/span> user_tab_columns<\/span><\/span>\n <\/span>WHERE<\/span> table_name = <\/span>'T'<\/span><\/span>\n   <\/span>AND<\/span> column_name = <\/span>'IND'<\/span>;<\/span><\/span>\n<\/span>\nNUM_DISTINCT<\/span><\/span>\n------------<\/span><\/span>\n           <\/span>2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Unfortunately, the histogram for the column ind<\/code> is ignored. Why? Because the Oracle Database has no idea what the value of const_boolean.true#<\/code>\u00a0 is. Hence, a histogram is not helpful in finding an execution plan.<\/p>\n\n\n\n
An optimal plan would look like this:<\/p>\n\n\n\n
Good execution plan<\/span><\/path><\/path><\/svg><\/span>
SQL_ID  bstdc2tsv1qcw, child number 0<\/span><\/span>\n-------------------------------------<\/span><\/span>\nSELECT count(*) AS open_count  FROM t  WHERE ind = 1<\/span><\/span>\n <\/span><\/span>\nPlan hash value: 3365671116<\/span><\/span>\n <\/span><\/span>\n-------------------------------------------------------------------------------<\/span><\/span>\n| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |<\/span><\/span>\n-------------------------------------------------------------------------------<\/span><\/span>\n|   0 | SELECT STATEMENT  |           |       |       |     1 (100)|          |<\/span><\/span>\n|   1 |  SORT AGGREGATE   |           |     1 |     3 |            |          |<\/span><\/span>\n|*  2 |   INDEX RANGE SCAN| T_IND_IDX |   101 |   303 |     1   (0)| 00:00:01 |<\/span><\/span>\n-------------------------------------------------------------------------------<\/span><\/span>\n <\/span><\/span>\nPredicate Information (identified by operation id):<\/span><\/span>\n---------------------------------------------------<\/span><\/span>\n <\/span><\/span>\n   2 - access("IND"=1)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
When you look at line 12, you see that<\/p>\n\n\n\n
    \n
  1. an INDEX RANGE SCAN<\/code> is used and<\/li>\n\n\n\n
  2. the number of rows is estimated correctly.<\/li>\n<\/ol>\n\n\n\n
    We get this plan when using a literal 1<\/code>, a bind variable with the bind value 1<\/code> (thanks to bind variable peeking) or a constant with value 1<\/code> (which is treated as a bind variable in PL\/SQL).<\/p>\n\n\n\n
    The wrong cardinality is a major problem. Because the cardinality is the most important criterion for choosing an optimal access method, join order and join method. Bad cardinality estimates lead to bad execution plans and bad performance. This cannot be ignored, even if in this demo case the resulting performance is still okay.<\/p>\n\n\n\n
    The problem occurs only if we are accessing columns with significantly skewed data and if these columns have a histogram.<\/p>\n\n\n\n
    5. Workarounds<\/h2>\n\n\n\n
    We have basically three options to work around the problem:<\/p>\n\n\n\n
      \n
    1. For PL\/SQL code we can use a constant instead of a parameterless function
      (e.g. ind = const_boolean.co_true<\/code>)<\/li>\n\n\n\n
    2. For PL\/SQL code or plain SQL like in views, we can use a literal with a comment instead of a parameterless function
      (e.g. ind = 1 -- const_boolean.co_true<\/code>)<\/li>\n\n\n\n
    3. For PL\/SQL code or plain SQL like in views, we can query the parameterless function in a subquery and force the optimizer to execute it during parse time [added on 2019-12-14]<\/em>
      (e.g. ind IN (SELECT \/*+ precompute_subquery *\/ const_boolean.true# FROM DUAL)<\/code>)<\/em><\/li>\n<\/ol>\n\n\n\n
      The first option has the drawback, that you have to change the SQL to make it runnable outside of PL\/SQL. The second option may lead to inconsistencies due to wrong literal\/comment combinations or when changing constant values. The third option requires an IN condition<\/a> that could be accidentally changed to an equal comparison condition<\/a> due to the scalar subquery<\/a>, which would make the undocumented precompute_subquery hint<\/a> ineffective. [added on 2019-12-14]<\/em><\/p>\n\n\n\n
      Of course, you can continue to use parameterless functions in SQL and PL\/SQL and switch to one of the options if there is a problem or if you know that a histogram exists for a certain column. But this is difficult to apply consistently. In fact, it makes maintenance more complicated with a certain performance risk or penalty.<\/p>\n\n\n\n
      6. Considered Alternatives<\/h2>\n\n\n\n
      I had a look at Associate Statistics (Extensible Optimizer Interface)<\/a>. This does not help, because there is no way to access the related table columns to calculate the impact on the selectivity. The feature is useful if a function gets some parameters to calculate the impact on the selectivity, but without parameters, this is not possible.<\/p>\n\n\n\n
      I had considered list partitioned<\/a> tables based on skewed columns instead of using indexes. This works and can make sense to reduce the overhead of an index (especially indexing non-selective values). But the issues regarding parameterless functions are 100 percent the same.<\/p>\n\n\n\n
      7. Summary<\/h2>\n\n\n\n
      Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore find an optimal execution plan.<\/p>\n\n\n\n
      Actually, we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.<\/p>\n\n\n\n
      If you think I missed something important, especially if you think there is a better workaround or even a solution, then please do not hesitate to leave a comment or contact me directly. Thank you.<\/p>\n\n\n\n
      Updated on 2019-12-14, added a third option under “5. Workarounds” based on a tweet<\/a> by Jonathan Lewis<\/a>. Thanks a lot.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
      Do you use parameterless PL\/SQL functions in your queries? Did you know that this may cause performance issues? In this blog post, I explain why parameterless functions can be the reason for bad execution plans in any Oracle Database. I recently had to analyze this problem in a production system and thought [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":9432,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[25,13,85],"class_list":["post-9417","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-performance","tag-plsql","tag-sql"],"yoast_head":"\nConstants vs. Parameterless Functions - Philipp Salvisberg's Blog<\/title>\n<meta name=\"description\" content=\"Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Constants vs. Parameterless Functions - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-12-13T17:24:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T09:55:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png\" \/>\n\t<meta property=\"og:image:width\" content=\"686\" \/>\n\t<meta property=\"og:image:height\" content=\"360\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Constants vs. Parameterless Functions\",\"datePublished\":\"2019-12-13T17:24:17+00:00\",\"dateModified\":\"2023-11-08T09:55:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/\"},\"wordCount\":1146,\"commentCount\":4,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/bad_execution_plan.png\",\"keywords\":[\"Performance\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/\",\"name\":\"Constants vs. Parameterless Functions - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/bad_execution_plan.png\",\"datePublished\":\"2019-12-13T17:24:17+00:00\",\"dateModified\":\"2023-11-08T09:55:47+00:00\",\"description\":\"Parameterless functions are a way to use constants in SQL outside of PL\\\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\\\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/bad_execution_plan.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/bad_execution_plan.png\",\"width\":686,\"height\":360},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2019\\\/12\\\/13\\\/constants-vs-parameterless-functions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Constants vs. Parameterless Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Constants vs. Parameterless Functions - Philipp Salvisberg's Blog","description":"Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/","og_locale":"en_US","og_type":"article","og_title":"Constants vs. Parameterless Functions - Philipp Salvisberg's Blog","og_description":"Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.","og_url":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2019-12-13T17:24:17+00:00","article_modified_time":"2023-11-08T09:55:47+00:00","og_image":[{"width":686,"height":360,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Constants vs. Parameterless Functions","datePublished":"2019-12-13T17:24:17+00:00","dateModified":"2023-11-08T09:55:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/"},"wordCount":1146,"commentCount":4,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png","keywords":["Performance","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/","url":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/","name":"Constants vs. Parameterless Functions - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png","datePublished":"2019-12-13T17:24:17+00:00","dateModified":"2023-11-08T09:55:47+00:00","description":"Parameterless functions are a way to use constants in SQL outside of PL\/SQL, for example in views. However, they rob the optimizer of the capability to use histograms and therefore finding an optimal execution plan. Actually we can only work around the problem. The Oracle Database has to provide the solution. Either by allowing to access package global constants in SQL (outside of PL\/SQL) or by implementing some kind of peeking for parameterless, deterministic functions.","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/12\/bad_execution_plan.png","width":686,"height":360},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2019\/12\/13\/constants-vs-parameterless-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Constants vs. Parameterless Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/9417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=9417"}],"version-history":[{"count":44,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/9417\/revisions"}],"predecessor-version":[{"id":12662,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/9417\/revisions\/12662"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/9432"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=9417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=9417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=9417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}