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…