Deterministic Functions

I would like to show how to improve your queries by using deterministic functions. Deterministic functions has got “memory”. It remembers result returned by called function in its “memory”. So next time the function is called with the same parameters it returns immediately result from “memory” instead of running function.

“Memory” persists only for time of execution of a SQL. It’s not persistent on session or instance level.

Let’s create simple deterministic function

CREATE OR REPLACE FUNCTION test_det(id NUMBER)
RETURN NUMBER
deterministic
AS
BEGIN
  DBMS_LOCK.sleep(1);
  RETURN id;
END;
/

Let’s prepare test data

DROP TABLE test;
 
CREATE TABLE test(id NUMBER);
 
BEGIN
  FOR i IN 1..10
  LOOP
    FOR j IN 1..5
    LOOP
      INSERT INTO test VALUES(j);
    END LOOP;
  END LOOP;
  COMMIT;
END;
/

We have 5 groups with 10 records each

SELECT id, COUNT(*) FROM test
GROUP BY id
ORDER BY id;
 
ID COUNT(*)
-- --------
 1       10 
 2       10 
 3       10 
 4       10 
 5       10

Let’s run test for deterministic function

SET timing ON
SELECT id, test_det(id) FROM test WHERE id=1
 
ID TEST_DET(ID)
-- ------------
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 
 10 rows selected 
 
Elapsed: 00:00:01.011

Function was called 10 times but executed only once, so overall time is close to 1 sec.

SET timing ON
SELECT test_det(id), COUNT(*) 
FROM test
GROUP BY test_det(id);
 
TEST_DET(ID) COUNT(*)
------------ --------
           1       10 
           2       10 
           4       10 
           5       10 
           3       10 
 
Elapsed: 00:00:05.018

Deterministic function was called 50 times but executed only 5 times. For another 45 cases results was taken from function “memory”.

If we run this query again in our session we have to wait again 5 seconds because function “memory” is not preserved on session, instance level.

SET timing ON
SELECT test_det(id), COUNT(*) 
FROM test
GROUP BY test_det(id);
 
TEST_DET(ID) COUNT(*)
------------ --------
           1       10 
           2       10 
           4       10 
           5       10 
           3       10 
 
Elapsed: 00:00:05.010

I hope you like it 🙂

Tomasz

DBAORA

oracle expert presents

Deterministic Functions

One thought on “Deterministic Functions”

Leave a Reply Cancel reply