tag:blogger.com,1999:blog-8677649049588007585.post8412369893205939774..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Nuances of Deterministic Functions in PL/SQL (9588)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-8677649049588007585.post-60525759319923401842013-03-15T20:47:46.652+00:002013-03-15T20:47:46.652+00:00Many thanks, Sayan. These articles are very intere...Many thanks, Sayan. These articles are very interesting and thorough.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-33780591565061732422013-03-13T01:43:11.594+00:002013-03-13T01:43:11.594+00:00Steven, Iudith,
some of the points are not quite...Steven, Iudith, <br /><br />some of the points are not quite correct. i wrote about it in details with examples now:<br />http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/<br /><br />Iudith, btw, about caching mechanism of deterministic functions in SQL engine i wrote here:<br />http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/Sayan Malakshinovhttps://www.blogger.com/profile/11087163803358489777noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-63491548844072981832011-11-30T18:08:44.597+00:002011-11-30T18:08:44.597+00:00(continued)
/* if we use the return value (displa...(continued)<br /><br />/* if we use the return value (display "n"), then the function is executed once, <br />even if the counter initialization and the loop are in a single block (server call).<br />*/<br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> plch_pkg.g_counter := 0;<br /><br /> FOR indx IN 1 .. 10000000<br /> LOOP<br /> n := plch_getdata (1);<br /> END LOOP;<br /><br /> dbms_output.put_line('n='||n);<br /><br /> dbms_output.put_line('g_counter='||<br /> plch_pkg.g_counter);<br />END;<br />/<br />n=1<br />g_counter=1<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />/* non-deterministic function */<br />CREATE OR REPLACE FUNCTION plch_getdata (n NUMBER)<br /> RETURN NUMBER<br /> -- DETERMINISTIC<br />IS<br />BEGIN<br /> plch_pkg.g_counter := plch_pkg.g_counter + 1;<br /> RETURN n;<br />END;<br />/<br /><br />Function created.<br /><br />/* here the count is correct, even with a single block and still without using the return value */<br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> plch_pkg.g_counter := 0;<br /><br /> FOR indx IN 1 .. 10000000<br /> LOOP<br /> n := plch_getdata (1);<br /> END LOOP;<br /><br /> dbms_output.put_line('g_counter='||<br /> plch_pkg.g_counter);<br />END;<br />/<br />g_counter=10000000<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />So, it looks like the number of executions of the function is determined NOT only by declaring it as DETERMINISTIC or not, but also by other decisions that the optimizing compiler may take based on this declaration.<br /><br />In other words, when we declare a function as DETERMINISTIC we are in fact saying to the pl/sql compiler:<br />"Take that function and "act upon it as you (the compiler) wish", I don't even care whether it is not executed at all, if you so decide" ...<br /><br />So, it is entirely our responsibility to make sure that the results will not be affected<br />by the optimizer taking "practically ANY" decision regarding the function execution.<br /><br />In my opinion, the fact that the optimization happens at compile time and thus only for arguments<br />that are static at compile time makes this feature much weaker than the result cache feature,<br />at least at the PL/SQL level, its only advantage upon the result cache being its speed.<br /><br />It is interesting however that, in the SQL layer, a deterministic function is able to behave as expected, though here the input values are received at run time only,<br />so this behavior is realy closer to a "query level result_cache-like behavior" <br />than to how the deterministic function behaves in PL/SQL.<br /><br />Thanks & Best Regards,<br />Iudith MentzelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-74176247625526829362011-11-30T18:07:59.791+00:002011-11-30T18:07:59.791+00:00Hello All,
I also made some inquiries around the D...Hello All,<br />I also made some inquiries around the DETERMINISTIC function issue on the same day of the quiz and found the following, tested in <br />11.1.0.7.0<br /><br />SET SERVEROUTPUT ON SIZE 1000000<br /><br />-- package for counting number of executions<br />CREATE OR REPLACE PACKAGE plch_pkg<br />AS<br /> g_counter NUMBER := 0;<br />END plch_pkg;<br />/<br /><br />Package created.<br /><br /><br />/* deterministic function <br />Oracle IS NOT aware of the side effect, it just takes our "deterministic" declaration <br />for granted<br />*/<br />CREATE OR REPLACE FUNCTION plch_getdata <br /> (n NUMBER)<br /> RETURN NUMBER<br /> DETERMINISTIC<br />IS<br />BEGIN<br /> plch_pkg.g_counter := plch_pkg.g_counter + 1;<br /> RETURN n;<br />END;<br />/<br /><br />Function created.<br /><br />/* if the counter initialization and the loop are in the same block, <br />the function is NOT EXECUTED AT ALL <br />( probably because its return value is in fact NOT used ! )<br />*/<br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> plch_pkg.g_counter := 0;<br /><br /> FOR indx IN 1 .. 10000000<br /> LOOP<br /> n := plch_getdata (1);<br /> END LOOP;<br /><br />dbms_output.put_line('g_counter='||<br /> plch_pkg.g_counter);<br />END;<br />/<br />g_counter=0<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />/* put counter initialization in a separate block (and server call)<br />*/<br />BEGIN<br /> plch_pkg.g_counter := 0;<br />END;<br />/<br /><br />/* now the function will be executed ONCE only (as expected for a DETERMINISTIC function)<br />*/<br />DECLARE<br /> n NUMBER;<br />BEGIN<br /> -- plch_pkg.g_counter := 0;<br /><br /> FOR indx IN 1 .. 10000000<br /> LOOP<br /> n := plch_getdata (1);<br /> END LOOP; <br /><br /> dbms_output.put_line('g_counter='||<br /> plch_pkg.g_counter);<br />END;<br />/<br />g_counter=1<br /><br />PL/SQL procedure successfully completed.<br /><br />( to be continued )Anonymousnoreply@blogger.com