-
Notifications
You must be signed in to change notification settings - Fork 24
/
udf.sql
75 lines (60 loc) · 1.25 KB
/
udf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
create or replace package tq84 is
function f (n number) return number;
function f_udf (n number) return number;
end tq84;
/
create or replace package body tq84 is
function f (n number) return number is
begin
return n-1;
end f;
function f_udf (n number) return number is
pragma udf;
begin
return n-1;
end f_udf;
end tq84;
/
show errors
set timing on
--
-- Note how the answer time is faster when the pragma-udf
-- function is used.
--
select sum(tq84.f (sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835012
-- Elapsed: 00:00:01.21
select sum(tq84.f_udf(sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835017
-- Elapsed: 00:00:00.81
select sum(tq84.f (sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835023
-- Elapsed: 00:00:01.19
select sum(tq84.f_udf(sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835029
-- Elapsed: 00:00:00.85
select sum(tq84.f (sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835035
-- Elapsed: 00:00:01.21
select sum(tq84.f_udf(sysdate-created)) s from dba_objects;
--
-- S
-- ----------
-- 402835041
-- Elapsed: 00:00:00.89
drop package tq84;