Why does a deterministic function execute an extra time in SQL?

Question

Given the following table

create table tmp_test as
 select mod(level, 5) as n
   from dual
connect by level <= 10
        ;

and this function

create or replace function test_deterministic (Pn in number
         ) return number deterministic is
begin
   dbms_output.put_line(Pn);
   dbms_lock.sleep(1);
   return Pn;
end;

It executes 6 times, taking 6 seconds:

SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)
---------------------
                    1
                    2
                    3
                    4
                    0
                    1
                    2
                    3
                    4
                    0

10 rows selected.

1
2
3
4
0
1
Elapsed: 00:00:06.02

I would have expected this to execute 5 times. If I run this SELECT statement in SQL Developer or PL/SQL Developer it only executes 5 times. Equally, if I run this in Pl/SQL it executes 5 times:

SQL> begin
  2     for i in ( select test_deterministic(n) from tmp_test ) loop
  3        null;
  4     end loop;
  5  end;
  6  /
1
2
3
4
0
Elapsed: 00:00:05.01

Why is this function executed 6 times when called in SQL from SQL*Plus? I expected it to execute 5 times instead.

I'm on version 11.2.0.3.5 and the SQL*Plus client is release 11.2.0.1.0 (64bit).

Fictive

You haven't defined any data types anywhere. Data types between the two environments are different, even with the same names. Try defining a data type in your table definition and also save your level in there to get the full picture. – Fester

It doesn't make any difference @ElectricLlama. Once the table is created the column N has a datatype, NUMBER. The function is then executed on the table and so is unrelated to it; it's something to do with deterministic (I think?). – Fictive

Interesting. I see the same thing in 10g (10.2.0.5), FWIW. – Circadian

Answer 1

Blame SQL*Plus, Ben. Your function works, in this situation, correctly. The extra value(1) you see is there because of arraysize value, and, mostly because of the way how SQL*Plus fetches rows. It first fetches first row and only then it starts to use arraysize for subsequent fetches. Every new fetch is a new database call, which forces your deterministic function to be evaluated. Try to set the arraysize to 1 or 2(same effect) and execute your select statement. The first row returns, and then, arraysize comes to play and each subsequent fetch will return couple of rows:

Arraysize is set to 1(two in fact)

SQL> set arraysize 1;
SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)                                                           
---------------------                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           

10 rows selected.

1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
Elapsed: 00:00:10.10

The same query with much larger arraysize:

SQL> set arraysize 50;
SQL> select test_deterministic(n) from tmp_test;

TEST_DETERMINISTIC(N)                                                           
---------------------                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           
                    1                                                           
                    2                                                           
                    3                                                           
                    4                                                           
                    0                                                           

10 rows selected.

1                                                                               
2                                                                               
3                                                                               
4                                                                               
0                                                                               
1                                                                               
Elapsed: 00:00:06.06

SQL> spool off;   

Any other client, whether it's SQL Developer or PL/SQL Developer lacks such behavior and gives correct output.

Calista

Ugh, that's horrible. I assume it works in a PL/SQL context in SQL*Plus because the SQL is being executed through the PL/SQL engine rather than the SQL then? – Fictive

The reason why SQL Developer "lacks that behaviour" is that you can't control the arraysize there. In JDBC you can define a "fetchsize" which is equivalent to the arraysize in SQLPlus. If you use a SQL client where you can control the fetch size, you can reproduce the SQLPlus behaviour in a JDBC based client as well. – Contracted

Actually in SQL Develop you can set the fetchsize but not below 50 (which is the default size). So if you increase the sample data in the table beyond 50 you will see the same behavior in SQL Developer – Contracted

@NicholasKrasnov: yes I found that out after posting my comment ;) – Contracted

Hmm, the options dialog says SQL array fetch size (between 50 and 200) - but apparently it does accept lower values. – Contracted

...I didn't think caching was guaranteed behavior anyways. – Phrenetic

No it's not guaranteed @Clockwork, but if it works for this function and table in one client you expect it to work in another. If it didn't work for this function anywhere I'd be more aggrieved but more understanding. – Fictive

@Fictive I assume it works in a PL/SQL context.. rather the SQL then? No, I do not thinks so. It's just the way SQL*PLUS does OCI calls(OCIStmtFetch() for some reason instead of OCIStmtFetch2()) and it looks like it does pre-fetching(1 st row) and then uses arraysize, and this is an environmental difference - deterministic functions are quite sensitive to environmental differences, thus extra evaluation. – Calista