Skip to main content

Deterministic functions, caching, and worries about consistent data

A developer contacted me with the following questions last week:

We have created a function that returns a single row column value form a query. When we call this function with the same input values it takes to long to return. Example:
select max (det_function('A2')) from dual connect by rownum <= 1000000
But when we change the function to a deterministic function the statement returns really fast. The only thing where we are unsure is what happens when the tables has changed to which the statement of the function selects? Do we need a to commit this table to bring oracle to re-execute the statement in the function and not use the cache or what should we do to get a consistent return value?

   v_ident   NUMBER;
     FROM my_table
    WHERE id = v_in_id;

   RETURN v_ident;

A function is deterministic if the value returned by the function is determined entirely by its input(s).

The following function, for example, is deterministic:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

You can also quickly see, I hope, that any function that contains SQL (like the first function defined above) cannot possibly be deterministic: it depends on the contents of one or more tables to do its job, and those datasets are not passed as IN parameters.

Does that mean the compiler will complain? No! But it does mean that you could create real problems for yourself if you are not careful about your use of this keyword.

So the rule should be: Only add the DETERMINISTIC keyword to truly deterministic functions.

Why? Why should it matter? Because under certain circumstances (such as the one identified by the developer above), Oracle Database will not execute your function, but instead simply use a previously cached return value.

Within the scope of a single server call (e.g., execution of a PL/SQL block), Oracle Database will keep track of input and return values for your deterministic functions. If in that same server call, you pass the same input values to the function, the database engine may choose to not actually execute the function, but instead simply pass back the previously-computed return value (for those same inputs).

That's why this developer saw such a great leap forward in performance.

Once that SELECT statement finishes, though, memory for the cache is released. When and if that same query is run again, the engine will start rebuilding and using that cache.

While that statement executing, though, no matter what sort of changes are made to the table, no matter if a commit is issued or not, those changes will not be visible to the statement that called the function.

That's why I will repeat The Rule again:

Only add the DETERMINISTIC keyword to truly deterministic functions.

If your function contains a SELECT statement and you want to call it from a SELECT statement, the best thing to do is take the SQL out of the function and "merge" it into your SQL - in other words, no user-defined functions. Just SQL.

Rob van Wijk offers lots more details on the behavior and performance of deterministic functions here. You will also be well-served to read Bryn Llewellyn's in-depth exploration of How to write a safe result-cached function.

Rather than repeat all those findings, I will simply conclude with:

1. Use the DETERMINISTIC function primarily as a way to document to future developers that your function is currently free of side effects, and should stay that way.

2. If you are looking for ways to improve the performance of functions executed inside SQL, learn more about the UDF pragma (new in Oracle Database 12c Release 1).

3. See if the function result cache feature (also explored in Bryn's blog post) might be applicable to your situation.

4. Do not call user-defined functions from SQL statements that in turn contain SQL statements (or at least do so with extreme caution). That SQL inside the function is not part of the same read-consistent image as the data set identified by the "outer" SQL.


  1. It would be useful to have some way of identifying functions as being "deterministic within a transaction." For functions based on slow-changing data.

    I'm sure it's not at all uncommon to have functions which return values based on database tables, but where those values are not expected to change often (if at all). I've often seen a "system parameter" table to hold settings, and a function written to access them easily. These functions are liable to be mislabelled as deterministic, even though they're not, because it's usually OK to treat them that way. Of course that's a bad idea because of the 1% of times when it causes real problems.

    It'd be good to have a way to deal with this without lying to the database about a function being deterministic.

  2. Just use the function result cache?

  3. Why can't we use the combination of deterministic and result_cache relies on <> together.
    What would be the impact of using both of them together ?


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…