When does a deterministic function use the previous calculated value?

Question

Consider a deterministic function like:

CREATE OR REPLACE FUNCTION SCHEMA.GET_NAME(ss_id nvarchar2
                     ) RETURN nvarchar2 DETERMINISTIC IS
    tmpVar nvarchar2(500);
    BEGIN

        select name into tmpvar from logistics.organization_items 
         where id = ss_id ;
        return tmpvar ;  

    END ss_name;

Using Toad I called the SCHEMA.GET_NAME(1) and it returns A. I then changed the value from the table from A to B and recalling the SCHEMA.GET_NAME(1) returned B.

It is a good result. But I'm afraid of the value not being updated according to this page in the documentation, which said:

When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views.

In what situations would the value of GET_NAME(1) return an old cached value (A instead of B)?

Answer 1

If you select from a table then the results of your function are not deterministic. A deterministic system is one that will always produce the same output, given the same initial conditions.

It is possible to alter the information in a table, therefore a function that selects from a table is not deterministic. To quote from the PL/SQL Language Reference:

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so are not captured if the database chooses not to reexecute the function.

In other words, Oracle does not guarantee that the results of the function will be accurate (they just might be). If your table is static, and unlikely to ever change, then it should be okay but this is not something I'd ever like to rely on. To answer your question, do not assume that Oracle will return anything other than the cached value within the same transaction/session.

If you need to speed this up there are two ways. Firstly, check that you have an index on ID!

1. Simply JOIN to this table. If your function is only this then there is no need for the function to exist.

2. Use scalar sub-query caching (not necessarily possible but worth the try).

   select ( select get_name(:id) from dual )
      from your_table
   

   Oracle will create an in-memory hash of the results of the function, like a result cache. If you're executing the same function multiple times then Oracle will hit the cache rather than the function.

Answer 2

Ben's answer sums it up nicely, and I would just like to add that the way you used DETERMINISTIC keyword inside your function is not right - keeping in view that you are reading the value from a table and then returning the same to the user.

A deterministic function should be used in cases, where you are evaluating an expression over a fixed input, for example, when you need to return a substring, or upper/lower case for the input string. Programatically, you know that for the same input the lowercase function will always return the same value, and so you would like to cache the result (using deterministic keyword).

When you read a value from a table, Oracle has no way to know that the value in the column has not changed, and so it prefers to rexecute the function and not depend on the cached result (which makes sense)