Here’s a new feature that might have slipped under the radar
for you with 12c. You can now have
PL/SQL functions defined in the WITH clause. First off this brings some cool functionality
to your select statements, but even cooler is that your statement will run faster,
a lot faster potential.
Here’s a simple test to show this. The ORD9 table has 103,120 rows in 1,390
blocks. The GMT_ORDER_DATE column represents
the order data in the number of seconds since midnight 1 January of the year
the order was placed. It has no indexes and does have full (100%) stats. I have a function defined below which returns
a date as it relates to the UNIX epoch. (Why you would really want to do this, I’m not
sure, but it’s here mostly to have it do something.)
create or replace function rtn_date_convert
(p_unix_gmt in number)
return date
as
v_date date;
begin
v_date := to_date('01/01/1970','mm/dd/yyyy') + (p_unix_gmt/86400) ;
RETURN v_date;
end ;
/
Now I have a select which uses this function as such:
select count(*) from ord9 where
rtn_date_convert(gmt_order_date) > sysdate - 3650
/
Next I have a query that does the same thing but has the function
defined within a with clause:
with function rtn_date_convert2 (p_unix_gmt
in number)
return date
as
v_date date;
begin
v_date := to_date('01/01/1970','mm/dd/yyyy') + (p_unix_gmt/86400) ;
RETURN v_date;
end ;
select count(*) from ord9
where rtn_date_convert2(gmt_order_date) >
sysdate - 3650
/
I run each several times (more then 10). Each query “does the same thing”, the plans
are identical. They both do a full scan
on the ORD9 table then do an aggregation of the data, just as you’d expect. LIOs and other stats are the same between the plans;
however the one with the internal function runs consistently in less than half
the time of the external one (this is output from our harness tool which
captures stats and trace files about SQL statements):
withfun: withfun:
TYPE
NAME
externalfun internalfun DIFFERENCE
----- ----------------------------
----------- ----------- ----------
Latch cache buffers chains 2,960 2,964 -4
row cache objects
157 151 6
shared pool
18 17 1
Stats buffer is pinned count 0 0 0
consistent gets
1,326 1,326 0
db block changes 0
0 0
db block gets
0 0 0
execute count
5 5 0
index fast full scans (full)
0 0 0
parse count (hard)
0 0 0
parse count (total)
6 6 0
physical reads
0 0 0
physical writes
0 0 0
redo size
0 0 0
session logical reads
1,326 1,326 0
session pga memory
0 0 0
session pga memory max
0 0 0
session uga memory
0 0 0
session uga memory max
0 0 0
sorts (disk)
0 0 0
sorts (memory)
0 0 0
sorts (rows)
0 0 0
table fetch by rowid
0 0 0
table scan blocks gotten
1,316 1,316 0
table scans (long tables)
0 0 0
table scans (short tables)
1 1 0
Time
elapsed time (centiseconds)
63 29 34
withfun:externalfun
STAT #565587040 id=1 cnt=1 pid=0 pos=1 obj=0
op='SORT AGGREGATE (cr=1326 pr=0 pw=0 time=621435 us)'
STAT #565587040 id=2 cnt=103120 pid=1 pos=1
obj=102779 op='TABLE ACCESS FULL ORD9 (cr=1326 pr=0 pw=0 time=608109 us
cost=282 size=824960 card=103120)'
withfun:internalfun
STAT #407419720 id=1 cnt=1 pid=0 pos=1 obj=0
op='SORT AGGREGATE (cr=1326 pr=0 pw=0 time=284779 us)'
STAT #407419720 id=2 cnt=103120 pid=1 pos=1
obj=102779 op='TABLE ACCESS FULL ORD9 (cr=1326 pr=0 pw=0 time=272208 us
cost=282 size=824960 card=103120)'
These tests were run
on a Windows 12.1.0.1 database, which interestingly had optimizer_features_enable
set to 11.2.0.2.
