Dave Webster's Blog

Tinkering with Oracle

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…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: