DETERMINISTIC Functions

In my opinion, DETERMINISTIC functions are not very well explained. Some DBAs and Developers know that a Function must be DETERMINISTIC if it is to be used to create a Function Based Index. Also, common understanding is that a Function can be DETERMINISTIC if it is guaranteed to return always return the same output value if the input value supplied is not changed.

Some explanation is in the documentation on the CREATE FUNCTION command.

"Indicates that the function returns the same result value whenever it is called with the same values for its parameters."

Yet, how does Oracle optimise the execution of a DETERMINISTIC function ? The documentation says "When the database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function."

Jonathan Lewis has also explored DETERMINSTIC functions here.

I decided to run my own tests here. I created a DETERMINISTIC function and had it executed once for each row in a 16-row result set :

SQL> create or replace function div_by_hundred (value_in number) return number DETERMINISTIC
2  as
3  divided_by_hundred number;
4  begin
5     select trunc(value_in/100) into divided_by_hundred from dual;
6     return divided_by_hundred;
7  end;
8  /

Function created.

SQL>
SQL>
SQL> drop table my_objects purge;

Table dropped.

SQL>
SQL> create table my_objects (obj_id number , obj_name varchar2(30), created date);

Table created.

SQL>
SQL> insert into my_objects
2  select object_id, object_name,created
3  from dba_objects
4  where owner in ('HEMANT','SCOTT')
5  /

16 rows created.

SQL>
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> select obj_id, div_by_hundred(obj_id)
2  from my_objects
3  order by obj_id
4  /

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
73179                    731
73180                    731
73181                    731
73182                    731
73183                    731
73184                    731
82748                    827
82749                    827
82751                    827
85339                    853
85340                    853

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
85341                    853
85344                    853
85346                    853
85429                    854
85433                    854

16 rows selected.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>

The trace file shows that the function is actually executed 16 times.

select obj_id, div_by_hundred(obj_id)
from my_objects
order by obj_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.04          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.01       0.01          0          7          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.03       0.06          0          8          0          16


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 184

Rows     Row Source Operation
-------  ---------------------------------------------------
16  SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=208 card=16)
16   TABLE ACCESS FULL MY_OBJECTS (cr=7 pr=0 pw=0 time=120 us cost=3 size=208 card=16)


Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
SQL*Net message to client                       3        0.00          0.00
SQL*Net message from client                     3        0.08          0.10
********************************************************************************

SQL ID: dd7zz37sxfcsc
Plan Hash: 1388734953
SELECT TRUNC(:B1 /100)
FROM
DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     16      0.00       0.00          0          0          0           0
Fetch       16      0.00       0.00          0          0          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.00       0.00          0          0          0          16

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184     (recursive depth: 1)


Rows     Row Source Operation
-------  ---------------------------------------------------
1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

******************************************************************************** 

We know that every time we pass the same value to "trunc(:b1/100)", the result will always be the same. This function had to be executed 16 times because each value passed to the function was different -- each time a new OBJ_ID was being passed.

So, what happens if the same OBJ_ID is passed to the function each time ?

SQL> UPDATE my_objects set obj_id = 1000000;

16 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> pause Press ENTER to proceed with new session
Press ENTER to proceed with new session

SQL> connect hemant/hemant
Connected.
SQL>
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> select obj_id, div_by_hundred(obj_id)
2  from my_objects
3  order by obj_id
4  /

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000
1000000                  10000

16 rows selected.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>

This appears in the trace file :

select obj_id, div_by_hundred(obj_id)
from my_objects
order by obj_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.04          0          7          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.05          0          7          0          16

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184

Rows     Row Source Operation
-------  ---------------------------------------------------
    16  SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=208 card=16)
    16   TABLE ACCESS FULL MY_OBJECTS (cr=7 pr=0 pw=0 time=360 us cost=3 size=208 card=16)


Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                       3        0.00          0.00
 Disk file operations I/O                        1        0.00          0.00
 SQL*Net message from client                     3        0.05          0.08
********************************************************************************

SQL ID: dd7zz37sxfcsc
Plan Hash: 1388734953
SELECT TRUNC(:B1 /100)
FROM
DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1


Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

Now, the function is executed only once ! Apparently, Oracle actually "peeks" (for want of a better word) at the value passed to the function. If it is the same as the last value (or one of the last four values ?-- see Jonathan Lewis's last paragraph in his note), it decides to not re-execute the function. That also means, that it holds the return value from the previous execution and re-uses that value -- to be returned again for the next row.

Will this behaviour hold true with DATEs ?

Stay tuned. .......

.

.

.