When does a determinate function use the previous calculated value?

Question

Consider a deterministic function of the type:

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 SCHEMA.GET_NAME(1) and it returns A Then I changed the value from the table from A to B and called SCHEMA.GET_NAME(1) returned by B

This is a good result. But I'm afraid that the value is not updated according to this page in the documentation , which says:

When an Oracle Database encounters a deterministic function in one of these contexts, it tries to use previously calculated results when possible, rather than repeating this function. If you subsequently change the semantics of a function, you must manually rebuild all dependent functional indexes and materialized representations.

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

Answer 1

If you select from the table, the results of your function will not be deterministic. A deterministic system is one that will always produce the same result under the same initial conditions.

You can change the information in the table, so the function that selects from the table is not deterministic. To quote 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 could affect the result of the function return. The results of this are not recorded if the database does not want to re-execute 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 is unlikely to ever change, then that should be fine, but that is not what I would ever want to rely on. To answer your question, don’t assume that Oracle will return nothing but a cached value within a single transaction / session.

If you need to speed this up, there are two ways. First, make sure you have an index on the ID !

• Just join this table. If your function is just that, then there is no need for a function to exist.

• Use scalar caching of subqueries (optional, but worth a try).

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

  Oracle will create a hash in the memory of the result of the function, as a result cache. If you execute the same function several times, then Oracle will go to the cache, not to the function.

Answer 2

Ben's answer summarizes it well, and I would like to add that the way you used the DETERMINISTIC keyword inside your function is incorrect - given that you read the value from the table and then return the same to the user.

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

When you read a value from a table, Oracle does not know that the value in the column has not changed, and therefore it prefers to reuse the function and does not depend on the result of caching (which makes sense)

Answer 3

Can you add a timestamp parameter to your function? Then pass sysdate to the function from where you call it.

Thus, you effectively cache the result, and you avoid running this function multiple times when it usually returns the same value within a given transaction.

Answer 4

Eres's remark is the answer I was looking for. Before executing a query or plsql block, you can use this decision power to execute this function again after resetting the return values ​​of the function (for example, changing the var package). I use this for: Select ... from big_table_vw;

Where

create the big_table_vw view in the form of select ... (analytic functions) from big_table where last_mutated> = get_date ();

In my case, big_table_vw contains window functions that prevent Oracle from pushing a predicate into a view.

Answer 5

This is a late answer to a long answer, but I just wanted to add that Oracle provides a caching mechanism for functions with mutable dependencies. RESULT_CACHE is an alternative to DETERMINISTIC , which allows Oracle to DETERMINISTIC results of the cache function at any time when the referenced object changes.

In this way, it is safe to cache expensive calculations with rarely updated objects that the cached results will not return incorrect results.

Here is an example of using mythological monsters:

 CREATE TABLE MONSTER ( MONSTER_NAME VARCHAR2(100) NOT NULL PRIMARY KEY ); INSERT INTO MONSTER VALUES ('Chthulu'); INSERT INTO MONSTER VALUES ('Grendel'); INSERT INTO MONSTER VALUES ('Scylla'); INSERT INTO MONSTER VALUES ('Nue'); COMMIT; CREATE OR REPLACE PACKAGE MONSTER_PKG IS FUNCTION IS_THIS_A_MONSTER(P_MONSTER_NAME IN VARCHAR2) RETURN BOOLEAN RESULT_CACHE; END MONSTER_PKG; / CREATE OR REPLACE PACKAGE BODY MONSTER_PKG IS FUNCTION IS_THIS_A_MONSTER(P_MONSTER_NAME IN VARCHAR2) RETURN BOOLEAN RESULT_CACHE RELIES_ON (MONSTER) IS V_MONSTER_COUNT NUMBER(1, 0) := 0; BEGIN SELECT COUNT(*) INTO V_MONSTER_COUNT FROM MONSTER WHERE MONSTER_NAME = P_MONSTER_NAME; RETURN (V_MONSTER_COUNT > 0); END; END MONSTER_PKG; / 

When a scenario like the one below occurs, any existing cache is invalid and the new cache may be rebuilt.

 BEGIN DBMS_OUTPUT.PUT_LINE('Is Kraken initially a monster?'); IF MONSTER_PKG.IS_THIS_A_MONSTER('Kraken') THEN DBMS_OUTPUT.PUT_LINE('Kraken is initially a monster'); ELSE DBMS_OUTPUT.PUT_LINE('Kraken is not initially a monster'); END IF; INSERT INTO MONSTER VALUES ('Kraken'); COMMIT; DBMS_OUTPUT.PUT_LINE('Is Kraken a monster after update?'); IF MONSTER_PKG.IS_THIS_A_MONSTER('Kraken') THEN DBMS_OUTPUT.PUT_LINE('Kraken is now a monster'); ELSE DBMS_OUTPUT.PUT_LINE('Kraken is not now a monster'); END IF; END; / 

Is Kraken originally a monster?
Kraken is not originally a monster
Is Kraken a monster after upgrade?
Kraken is now a monster