Before considering the use of a function in a realistic system, I suggest making a comparison. The test I did was quite simple, but I believe it is enough to show that the use of a function * in this * if it would be harming performance.
See the example, performed on a Linux HR with version 10G.
With the use of the function took
22 seconds to read 1 million and without, that is, only with the trunc () -
6 seconds . Considerable difference.
Select all
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