Blog
Função Determinística (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?
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.
Observe que a FUNCTION foi criada sem a expressão DETERMINISTIC. Abaixo vamos 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.
Vamos habilitar um trace da 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> ... ******************************************************************************** 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) ********************************************************************************
Observe que tivemos uma 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? Simples, porque fazemos doas chamadas da função na mesma query, deste modo temos o dobro de execuções baseado na quantidade de registros verificados.
Agora vamos criar a mesma função (F_EXEMPLO) porem especificando a function como sendo 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
Gerando um tkprof veja agora como ficou.
************************************************************************** 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) ********************************************************************************
Menos de um segundo foi necessário para executar toda a query.
E quantas chamadas da função F_EXEMPLO foi feita? Apenas duas 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.
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 removermos a expressão DETERMINISTC da função vamos receber o erro ORA-30533
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 semântica da função você terá que recriar manualmente os índices baseados em função (FBI) e materialized views.
256 visualizações
