.
\nLet’s say you have a PEOPLE table with FIRST_NAME and LAST_NAME and you want, in many places of your application, to display the full name. Usually my name will be displayed as ‘Franck Pachot’ and I can simply add a virtual column to my table, or view, as: initcap(FIRST_NAME)||’ ‘||initcap(LAST_NAME). Those are simple SQL functions. No need for procedural code there, right? But, one day, the business will come with new requirements. In some countries (I’ve heard about Hungary but there are others), my name may be displayed with last name… first, like: ‘Pachot Franck’. And in some context, it may have a comma like: ‘Pachot, Franck’.<\/p>\n
There comes a religious debate between Dev and Ops:<\/p>\n
And this is where we arrive in an unsustainable situation. Because we didn’t find a tradeoff between code maintainability and application performance, we get the worst from each of them: crazy resource usage for medium performance.<\/p>\n
However, in Oracle 20c, we have a solution for that. Did you code some C programs where you replace functions by pre-processor macros? So that your code is readable and maintainable like when using modules and functions. But compiled as if those functions have been merged to the calling code at compile time? What was common in those 3rd generation languages is now possible in a 4th generation declarative language: Oracle SQL.<\/p>\n
Let’s take an example. I’m building a PEOPLE table using the Linux \/usr\/share\/dict of words:<\/p>\n
\ncreate or replace directory \"\/usr\/share\/dict\" as '\/usr\/share\/dict';\ncreate table people as\nwith w as (\nselect *\n from external((word varchar2(60))\n type oracle_loader default directory \"\/usr\/share\/dict\" access parameters (nologfile) location('linux.words'))\n) select upper(w1.word) first_name , upper(w2.word) last_name\nfrom w w1,w w2 where w1.word like 'ora%' and w2.word like 'aut%'\norder by ora_hash(w1.word||w2.word)\n\/\n<\/code><\/pre>\nI have 100000 rows table here with first and last names.
\nHere is a sample:<\/p>\n
\nSQL> select count(*) from people;\n\n COUNT(*)\n----------\n 110320\n\nSQL> select * from people where rownum<=10;\n\nFIRST_NAME LAST_NAME\n------------------------------ ------------------------------\nORACULUM AUTOMAN\nORANGITE AUTOCALL\nORANGUTANG AUTHIGENOUS\nORAL AUTOPHOBIA\nORANGUTANG AUTOGENEAL\nORATORIAN AUTOCORRELATION\nORANGS AUTOGRAPHICAL\nORATORIES AUTOCALL\nORACULOUSLY AUTOPHOBY\nORATRICES AUTOCRATICAL\n<\/code><\/pre>\nPL\/SQL function<\/h3>\n
Here is my function that displays the full name, with the Hungarian specificity as an example but, as it is a function, it can evolve further:<\/p>\n
\ncreate or replace function f_full_name(p_first_name varchar2,p_last_name varchar2)\nreturn varchar2\nas\n territory varchar2(64);\nbegin\n select value into territory from nls_session_parameters\n where parameter='NLS_TERRITORY';\n case (territory)\n when 'HUNGARY'then return initcap(p_last_name)||' '||initcap(p_first_name);\n else return initcap(p_first_name)||' '||initcap(p_last_name);\n end case;\nend;\n\/\nshow errors\n<\/code><\/pre>\nThe functional result depends on my session settings:<\/p>\n
\nSQL> select f_full_name(p_first_name=>first_name,p_last_name=>last_name) from people\n where rownum<=10;\n\nFIRST_NAME,P_LAST_NAME=>LAST_NAME)\n------------------------------------------------------------------------------------------------\nOraculum Automan\nOrangite Autocall\nOrangutang Authigenous\nOral Autophobia\nOrangutang Autogeneal\nOratorian Autocorrelation\nOrangs Autographical\nOratories Autocall\nOraculously Autophoby\nOratrices Autocratical\n\n10 rows selected.\n\n<\/code><\/pre>\nBut let’s run it on many rows, like using this function in the where clause, with autotrace:<\/p>\n
\nSQL> set timing on autotrace on\nselect f_full_name(first_name,last_name) from people\nwhere f_full_name(p_first_name=>first_name,p_last_name=>last_name) like 'Oracle Autonomous';\n\nF_FULL_NAME(FIRST_NAME,LAST_NAME)\n------------------------------------------------------------------------------------------------------\nOracle Autonomous\n\nElapsed: 00:00:03.47\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 2528372185\n\n----------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1103 | 25369 | 129 (8)| 00:00:01 |\n|* 1 | TABLE ACCESS FULL| PEOPLE | 1103 | 25369 | 129 (8)| 00:00:01 |\n----------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n 1 - filter(\"F_FULL_NAME\"(\"P_FIRST_NAME\"=>\"FIRST_NAME\",\"P_LAST_NAME\"=>\n \"LAST_NAME\")='Oracle Autonomous')\n\n\nStatistics\n----------------------------------------------------------\n 110361 recursive calls\n 0 db block gets\n 426 consistent gets\n 0 physical reads\n 0 redo size\n 608 bytes sent via SQL*Net to client\n 506 bytes received via SQL*Net from client\n 2 SQL*Net roundtrips to\/from client\n 0 sorts (memory)\n 0 sorts (disk)\n 1 rows processed\n<\/code><\/pre>\n100000 recursive calls. That is bad and not scalable. The time spent in context switches from the SQL to the PL\/SQL engine is a waste of CPU cycles.<\/p>\n
Note that this is difficult to improve because we cannot create on index for that predicate:<\/p>\n
\nSQL> create index people_full_name on people(f_full_name(first_name,last_name));\ncreate index people_full_name on people(f_full_name(first_name,last_name))\n *\nERROR at line 1:\nORA-30553: The function is not deterministic\n<\/code><\/pre>\nYes, this function cannot be deterministic because it depends on many other parameters (like the territory in this example, in order to check if I am in Hungary)<\/p>\n
Update 25-FEB-2020<\/h3>\n
If you have read this post yesterday, pleased note that I’ve updated it. I initially didn’t add the territory parameter, thinking that changing NLS_TERRITORY would re-parse the query but it seems that cursors are shared across different territories. Anyway, the documentation<\/a> says:
\nAlthough the DETERMINISTIC property cannot be specified, a SQL macro is always implicitly deterministic.<\/i>
\nSo, better not relying on child cursor sharing. Thanks to Stew Ashtom for the heads up on that:<\/p>\n\nOuch, I was wrong… different NLS_TERRITORY share the same cursor \ud83d\ude31 pic.twitter.com\/BvPTLXWWpY<\/a><\/p>\n— Franck Pachot (@FranckPachot) February 25, 2020<\/a><\/p><\/blockquote>\n