Antes de considerar o uso de uma função em um sistema realístico, sugiro fazer um comparativo. O teste que eu fiz foi bastante simplório, mas creio que é suficiente para mostrar que o uso de uma função *neste* caso estaria prejudicando a performance.
Veja o exemplo, realizado em um Linux RH com a versão 10g.
com o uso da função demorou
22 segundos para ler 1 milhão e sem, ou seja, somente com o TRUNC() -
6 segundos. Considerável diferença.
Selecionar tudo
ops$marcio@LNX10GR2> create table t as
2 with x as
3 (
4 select trunc(sysdate)+ (level-1) dt
5 from dual
6 connect by 1=1 and level <= 1000000
7 )
8 select * from x
9 /
Table created.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select count(*) from t;
COUNT(*)
-------------
1000000
1 row selected.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace function f ( p_date in date )
2 return date as
3 begin
4 return trunc(p_date);
5 end;
6 /
Function created.
ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> set timing on
ops$marcio@LNX10GR2> set autotrace traceonly
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select f(dt) from t;
1000000 rows selected.
Elapsed: 00:00:22.21
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11804 consistent gets
0 physical reads
0 redo size
10300705 bytes sent via SQL*Net to client
110374 bytes received via SQL*Net from client
10001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
ops$marcio@LNX10GR2> select trunc(dt) from t;
1000000 rows selected.
Elapsed: 00:00:06.81
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11804 consistent gets
0 physical reads
0 redo size
10300709 bytes sent via SQL*Net to client
110374 bytes received via SQL*Net from client
10001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed