funcion - sintaxis sql server 2008

¿Por qué una función determinista ejecuta un tiempo extra en SQL?

Question

Dada la siguiente tabla

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

y esta función

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;

Se ejecuta 6 veces, tomando 6 segundos:

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

Hubiera esperado que esto se ejecutara 5 veces. Si ejecuto esta instrucción SELECT en SQL Developer o PL / SQL Developer, solo se ejecuta 5 veces. Igualmente, si ejecuto esto en Pl / SQL, se ejecuta 5 veces:

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

¿Por qué se ejecuta esta función 6 veces cuando se llama en SQL desde SQL * Plus? Esperaba que se ejecutara 5 veces.

Estoy en la versión 11.2.0.3.5 y el cliente SQL * Plus es la versión 11.2.0.1.0 (64 bits).

Answer 1

Culpa SQL * Plus, Ben. Su función funciona, en esta situación, correctamente. El valor extra ( 1 ) que ve está allí debido arraysize valor de arraysize y, sobre todo, a la forma en que SQL * Plus obtiene las filas. Primero obtiene la primera fila y solo luego comienza a usar arraysize para arraysize posteriores. Cada nueva búsqueda es una nueva llamada a la base de datos, lo que obliga a evaluar su función determinista. Intente configurar el arraysize en 1 o 2 (mismo efecto) y ejecute su instrucción de select . La primera fila retorna, y luego, entra en juego el arraysize y cada arraysize posterior devolverá dos filas:

Arraysize se establece en 1 (dos de hecho)

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

La misma consulta con un arraysize mucho más 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; 

Cualquier otro cliente, ya sea SQL Developer o PL / SQL Developer, carece de dicho comportamiento y proporciona el resultado correcto.