Função Deterministica (FUNCTION DETERMINISTIC)

As funções deterministicas existentes desde a versão 8i são conhecidas por muitos DBA’s e Desenvolvedores apenas como necessárias para a criação de índices baseados em função (Function based index – FBI). Certamente que elas são necessárias para as FBI, mas o que elas realmente fazem? ou melhor, proporcionam? 

Quando criamos uma function (create function) podemos especificar ou omitir a palavra DETERMINISTC. Quando especificada permite que a execução da SQL salve uma copia do resultado de retorno da função para poder prover maior performance nas chamadas subsequentes com o mesmo valor de entrada na mesma SQL sem a necessidade de reexecutar a função. O otimizador escolhe a melhor performance entre obter a copia dos resultados salva ou chamar novamente a função. 

Vou demonstrar como isto funciona e o ganho de performance.

SQL> create or replace function f_exemplo(value number) return number as
  2    retorno number;
  3  begin
  4    select trunc(value / 3) into retorno from dual;
  5    return retorno;
  6  end;
  7  /
 
Function created.

Observem que criei a FUNCTION SEM A PALAVRA DETERMINISTIC. Abaixo vou criar e popular uma tabela EXEMPLO.

SQL> create table exemplo(
  2     valor number(5)
  3  )
  4  /
 
Table created.
 
SQL> insert into exemplo select 10 from dual connect by level <=100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.

Vou habilitar um trace da minha sessão e executar a seguinte query:

SQL> alter session set tracefile_identifier=exemplo;
 
Session altered.
 
SQL> alter session set sql_trace=true;
 
Session altered.
 
SQL> select f_exemplo(valor), count(1)
  2    from exemplo
  3   where f_exemplo(valor) = 3
  4   group by f_exemplo(valor);
 
F_EXEMPLO(VALOR)   COUNT(1)
---------------- ----------
               3     100000
 
SQL> exit

Agora gerando um TKPROF e verificando o arquivo gerado (exemplo.txt)

serv1:oracle:dbteste> tkprof dbteste_ora_22508_EXEMPLO.trc exemplo.txt
 
TKPROF: Release 10.2.0.5.0 - Production on Tue Jun 11 17:30:30 2013
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
serv1:oracle:dbteste>
 

********************************************************************************
 
select f_exemplo(valor), count(1)
  from exemplo
 where f_exemplo(valor) = 3
 group by f_exemplo(valor)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      6.93       8.23         43        156          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      6.93       8.23         43        157          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH GROUP BY (cr=156 pr=43 pw=0 time=10872483 us)
 100000   TABLE ACCESS FULL EXEMPLO (cr=156 pr=43 pw=0 time=5500249 us)
 
********************************************************************************
 
SELECT TRUNC(:B1 / 3)
FROM
 DUAL
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 200000      1.58       1.80          0          0          0           0
Fetch   200000      0.73       0.84          0          0          0      200000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   400001      2.31       2.64          0          0          0      200000
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 200000  FAST DUAL  (cr=0 pr=0 pw=0 time=224070 us)
 
********************************************************************************

Observem que tivemos 1 execução (Execute) da query levando 8.23 segundos (elapsed). 

Mais abaixo temos a chamada que esta query fez sobre a função f_exemplo. Tivemos 200000 execuções (Execute) resultando em 2.64 segundos totais. 

Mas porque foram 200.000 execuções e não 100.000? Porque fazemos 2 chamadas da função na mesma query, deste modo temos o dobro de execuções baseado na quantidade de registros verificados. 

Agora vou recriar a mesma função (F_EXEMPLO) porem especificando a function como DETERMINISTIC.

SQL> create or replace function f_exemplo(value number) return number deterministic as
  2  retorno number;
  3  begin
  4  select trunc(value/3) into retorno from dual;
  5    return retorno;
  6   end;
  7  /
 
Function created.

Habilitamos novamente um trace da sessão e executamos a mesma query!

SQL> alter session set tracefile_identifier=exemplo;
 
Session altered.
 
SQL> alter session set sql_trace=true;
 
Session altered.
 
SQL> select f_exemplo(valor), count(1)
  2    from exemplo
  3   where f_exemplo(valor) = 3
  4   group by f_exemplo(valor);
 
F_EXEMPLO(VALOR)   COUNT(1)
---------------- ----------
               3     100000
 
SQL> exit

Gerei um tkprof, e vejam agora. Menos de 1 segundo foi necessário para executar toda a query. 

E quantas chamadas da função F_EXEMPLO foi feita? Apenas 2, em 0.00 segundos! Uma do select outra da condição (literais), isto porque ele recuperou o retorno salvo para as mesmas entradas do SQL, no caso o VALOR.

********************************************************************************
 
select f_exemplo(valor), count(1)
  from exemplo
 where f_exemplo(valor) = 3
 group by f_exemplo(valor)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.27       0.52         63        156          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.28       0.53         63        157          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH GROUP BY (cr=181 pr=63 pw=0 time=526929 us)
 100000   TABLE ACCESS FULL EXEMPLO (cr=181 pr=63 pw=0 time=304362 us)
 
********************************************************************************
 
SELECT TRUNC(:B1 /3)
FROM
 DUAL
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          0          0           2
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: ANDERSON   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      2  FAST DUAL  (cr=0 pr=0 pw=0 time=3 us)
 
********************************************************************************

COMPARATIVO:

	NON-DETERMINISTIC	DETERMINISTIC
Execuções	200000	2
Tempo	2.64	0.00

Como comentado, as funções DETERMINISTICAS também são necessárias para criar as FBI, observem:

SQL> create index exemplo_idx on exemplo(f_exemplo(valor));
 
Index created.

Agora se eu remover a DETERMINISTC da função vou receber o erro.

SQL> create index exemplo_idx on exemplo(f_exemplo(valor));
create index exemplo_idx on exemplo(f_exemplo(valor))
                                    *
ERROR at line 1:
ORA-30553: The function is not deterministic

Vale lembrar que se for alterado a semantica da função você terá que recriar manualmente os índices baseados em função (FBI) e materialized views. 

Espero que tenham gostado do artigo! dúvidas podem postar abaixo. 

Referência:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/function.htm