为什么确定性函数在SQL中执行额外的时间？

Question

给出下表

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

而这个功能

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;

它执行6次，耗时6秒：

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

我本以为这会执行5次。如果我在或PL/中运行此SELECT语句，它只执行5次。同样，如果我在Pl/SQL中运行它，它执行5次：

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

为什么在SQL中从SQL*Plus调用此函数时执行6次？我原以为它会执行5次。

我使用的是11.2.0.3.5版本，SQL*Plus客户端是11.2.0.1.0 (64位)版本。

Answer 1

怪罪SQL*Plus，Ben。在这种情况下，您的功能是正确的。您看到的额外值(1)之所以存在，是因为arraysize值，主要是因为SQL*Plus获取行的方式。它首先获取第一行，然后才开始对后续的获取使用arraysize。每个新的fetch都是一个新的数据库调用，它强制计算您的确定性函数。尝试将arraysize设置为1或2(相同的效果)并执行select语句。第一行返回，然后，arraysize开始播放，每个后续的fetch将返回几行：

Arraysize被设置为1(实际上是两个)

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

使用大得多的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;   

任何其他客户端，无论是还是PL/，都缺乏这种行为，并给出了正确的输出。