PL/SQL functions: Overhead when using in SQL

WHERE clause

This is a test with which I wanted to approximately estimate the impact on the SQL execution time when using PL/SQL functions in where clauses.

I conducted this test on two environments, both being Oracle 19. To my surprise, the loss of performance was quite different on those environments.

create table tq84_tab (
   nm number
);

insert into tq84_tab
select
   mod(
      a.n*13 +
      b.n*17 +
      c.n*19 ,
      100
   )
from
--
-- Prevent ORA-30009: Not enough memory for CONNECT BY operation
--   ( see http://nimishgarg.blogspot.com/2017/12/ora-30009-not-enough-memory-for-connect.html)
--
   (select level n from dual connect by level <= 1000) a,
   (select level n from dual connect by level <= 1000) b,
   (select level n from dual connect by level <=   50) c
;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/table.sql

create function tq84_func(nm number) return integer  is
begin
    if nm = 42 then return 1; end if;
    return 0;
end tq84_func;
/


create function tq84_func_deterministic(nm number) return integer deterministic is
begin
    if nm = 42 then return 1; end if;
    return 0;
end tq84_func_deterministic;
/


create function tq84_func_result_cache(nm number) return integer result_cache is
begin
    if nm = 42 then return 1; end if;
    return 0;
end tq84_func_result_cache;
/

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/funcs.sql

First, we select the «ordinary» count from the table …

select count(*) from tq84_tab;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-count.sql

… and compare with the using a where condition. Since there is no index on the table, a full table scan will be employed.

select count(*) from tq84_tab where nm = 42;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-where.sql

When using a function, on one environment, I found a 18 or 60 times slower execution:

select count(*) from tq84_tab where tq84_func              (nm) = 1;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-func.sql

Applying result_cache on the function resulted in a 77 or 23 times slower execution:

select count(*) from tq84_tab where tq84_func_result_cache (nm) = 1;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-result_cache.sql

When using a deterministic function, the execution was 13 or 5 times slower.

select count(*) from tq84_tab where tq84_func_deterministic(nm) = 1;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-deterministic.sql

Using a «local» function (with clause), the execution time was approximately 16 or 6 times slower.

with function tq84_func_local(nm number) return integer is
begin
    if nm = 42 then return 1; end if;
    return 0;
end tq84_func_local;
select count(*) from tq84_tab where tq84_func_local(nm) = 1;
/

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-local.sql

with function tq84_func_local(nm number) return integer deterministic is
begin
    if nm = 42 then return 1; end if;
    return 0;
end tq84_func_local;
select count(*) from tq84_tab where tq84_func_local(nm) = 1;
/

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/select-local-deterministic.sql

Cleaning up:

drop table    tq84_tab;
drop function tq84_func;
drop function tq84_func_deterministic;
drop function tq84_func_result_cache;

Github repository oracle-patterns, path: /PL-SQL/function-procedure/overhead-in-SQL/where/clean-up.sql

PL/SQL functions: Overhead when using in SQL

WHERE clause

See also