## Deterministic functions sometimes are Deterministic, even on 10.2

Posted by anotherdavewebster on August 24, 2010

A few years back, some colleagues of mine were bemoaning the fact that functions created as DETERMINISTIC weren’t actually implemented as such in 10.2. Just to be clear, this is no longer the case with Oracle 11. DETERMINISTIC functions actually are deterministic:

CREATE OR REPLACE FUNCTION non_det_fn( i_parameter IN NUMBER ) RETURN NUMBER IS BEGIN DBMS_OUTPUT.put_line( 'Called non_det_fn with i_parameter = ' || i_parameter ); RETURN i_parameter; END; CREATE OR REPLACE FUNCTION det_fn( i_parameter IN NUMBER ) RETURN NUMBER DETERMINISTIC IS BEGIN DBMS_OUTPUT.put_line( 'Called det_fn with i_parameter = ' || i_parameter ); RETURN i_parameter; END; DECLARE l_result PLS_INTEGER; BEGIN dbms_output.put_line('Standard function'); FOR i IN 1 .. 5 LOOP l_result := non_det_fn( 1 ); END LOOP; dbms_output.put_line('Deterministic function'); FOR i IN 1 .. 5 LOOP l_result := det_fn( 1 ); END LOOP; END;

You get pretty much what you would expect from dbms_output:

Standard function Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Deterministic function Called det_fn with i_parameter = 1

On 10.2, Oracle doesn’t yet implement the deterministic functionality and we get:

Standard function Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 1 Deterministic function Called det_fn with i_parameter = 1 Called det_fn with i_parameter = 1 Called det_fn with i_parameter = 1 Called det_fn with i_parameter = 1 Called det_fn with i_parameter = 1

Now this is not news of course but I did stumble on a rather surprising case where Oracle did implement the functionality in 10.2:

SELECT non_det_fn( mod_number ) , det_fn( mod_number ) FROM ( SELECT MOD( ROWNUM, 3 ) mod_number FROM DUAL CONNECT BY ROWNUM <= 10 );

dbms_output gives us:

Called non_det_fn with i_parameter = 1 Called det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 2 Called det_fn with i_parameter = 2 Called non_det_fn with i_parameter = 0 Called det_fn with i_parameter = 0 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 2 Called non_det_fn with i_parameter = 0 Called non_det_fn with i_parameter = 1 Called non_det_fn with i_parameter = 2 Called non_det_fn with i_parameter = 0 Called non_det_fn with i_parameter = 1

It’s not the same as the PL/SQL implementation in 11.1 but it’s certainly more than I was expecting and may well come in useful at some point. Of course, you can make all functions “deterministic” by calling them from Scalar Subqueries but that’s another matter…

## Leave a Reply