Search code examples
oracle-databaseplsqloracle11g

When does a deterministic function use the previous calculated value?

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)?

Solution

  • 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.