Why does a determinate function do 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 is executed 6 times, takes 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 expect this to be done 5 times. If I run this SELECT statement in SQL Developer or PL / SQL Developer, it will execute only 5 times. Similarly, if I run this in Pl / SQL, it runs 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 calling SQL from SQL * Plus? I expected it to be executed 5 times.

I am in version 11.2.0.3.5, and the SQL * Plus client is the 11.2.0.1.0 (64 bit) release.

Answer 1

Kill SQL * Plus, Ben. Your function works correctly in this situation. The extra value ( 1 ) that you see exists because of the arraysize value and mainly because of how SQL * Plus fetches the rows. First, it extracts the first row and only then begins to use arraysize for subsequent selections. Each new selection is a new database call that forces you to define your deterministic function. Try setting arraysize to 1 or 2 (same effect) and execute the select statement. The first line is returned, and then arraysize appears, and each subsequent selection returns a couple of lines:

arraysize set to 1 (two actually)

 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 SQL developer or PL / SQL developer, does not have this behavior and gives the correct result.