Skip to Main Content
  • Questions

  • Non-Deterministic Functions and Scalar Subquery Caching

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andreas.

Asked: March 20, 2019 - 11:53 am UTC

Last updated: March 27, 2019 - 7:16 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi,

i got one question regarding scalar subquery:

Why does Oracle not restrict scalar-subquery-caching to functions which are declared to be deterministic?

With the deterministic keyword the developer states that a function is deterministic. In my opinion every other function needs to be assumed to be potentially non-deterministic.


Currently scalar subquery may easily lead to wrong results, and I wonder why it was built this way. Of course one can say that an expert should know about those details, but its a bit counter-intuitive - isn't it? I bet that more than half of developers, which are knowing about scalar-subquery, are not aware of this pitfall.

I would appreciate a short opinion on it. Thanks a lot in advance.

Kind regards

and Connor said...

You make a good argument, but I think (this is my opinion, not any Oracle official position) that we're working on the basis that the overwhelming majority of functions will be deterministic even if not explicitly defined to be.

Ultimately we have to have faith the developers have a reasonable understanding of what they're doing. Even putting aside scalar subquery, I often see a lot of function calls in SQL where that function references sysdate or systimestamp, and its relatively apparent from the code that the developers are assuming that these will be fixed throughout the duration of the query...and well....

SQL> create or replace
  2  function f(i varchar2) return varchar2 is
  3  begin
  4    return i||'-'||to_char(systimestamp,'HH24MISS:FF');
  5    dbms_lock.sleep(0.5);
  6  end;
  7  /

Function created.

SQL>
SQL> select rownum, f(rownum) x
  2  from   dual
  3  connect by level <= 10;

    ROWNUM X
---------- ------------------------------
         1 1-165100:802000000
         2 2-165100:863000000
         3 3-165100:863000000
         4 4-165100:897000000
         5 5-165100:897000000
         6 6-165100:930000000
         7 7-165100:930000000
         8 8-165100:964000000
         9 9-165100:964000000
        10 10-165100:997000000

10 rows selected.


We'll just continue to do our best to educate developers on the importance of data correctness.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Usage of deterministic-clause

Andreas, March 26, 2019 - 9:42 am UTC

Thanks for the answer.

I mostly agree, especially on the fact that the majority of the functions is deterministic, although its not explicitly declared with the deterministic-clause.

If Oracle would generally treat all functions as being potentially non-deterministic and only allow deterministic functions in SQL-statements (which would be the correct handling in my opinion), then developers would have more reason to use the deterministic-clause.
(Or are there reasons to allow non-deterministic functions in SQL? Right now I cant think of any.)

Conclusio so-to-say: Watch out whether a function is deterministic before using it in an SQL-statement.
Connor McDonald
March 27, 2019 - 7:16 am UTC

then developers would have more reason to use the deterministic-clause


My cynical self thinks all that would happen is we'd see more "deterministic" keywords added to functions that have not truly been checked for determinism.

Watch out whether a function is deterministic before using it in an SQL-statement.


Yes indeed.

Caching demonstration

Dieter, March 28, 2019 - 3:07 pm UTC 

 with
 function f(i varchar2) return varchar2 is
 begin
   return i||'-'||to_char(systimestamp,'HH24MISS:FF');
   dbms_lock.sleep(0.5);
 end;
 select rownum,  f(rownum) x, (select f(rownum) from dual) y
  from   dual
  connect by level <= 10

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.