Search notes:

Oracle: Using a PL/SQL function in a WHERE clause

create or replace package tq84_pkg
    authid definer
as

    procedure init;
    function  val return number;
    function  det return number deterministic;
    procedure rep;

end tq84_pkg;
/


create or replace package body tq84_pkg
as

    cnt number;

    procedure init is begin
       cnt := 0;
    end init;

    function val return number is begin
       cnt := cnt + 1;
       return 42;
    end val;

    function det return number deterministic is
    begin
       cnt := cnt + 1;
       return 42;
    end det;

    procedure rep is begin
       dbms_output.put_line('cnt = ' || cnt);
       init;
    end rep;

begin
    init;

end tq84_pkg;
/


create table tq84_tab (
   txt  varchar2(10),
   num  number
);

insert into tq84_tab values('a',   7);
insert into tq84_tab values('b',  42);
insert into tq84_tab values('c',  42);
insert into tq84_tab values('d',  99);
insert into tq84_tab values('e',  42);
insert into tq84_tab values('f',null);

The function in the where condition is called 5 times:

select txt from tq84_tab where num = tq84_pkg.val;
exec tq84_pkg.rep

The function is called only once:

select txt from tq84_tab where num = (select tq84_pkg.val from dual);
exec tq84_pkg.rep

The deterministic function is called once as well:

select txt from tq84_tab where num = tq84_pkg.det;
exec tq84_pkg.rep

explain plan for
   select txt from tq84_tab where num = tq84_pkg.val;
select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- --------------------------------------
-- | Id  | Operation         | Name     |
-- --------------------------------------
-- |   0 | SELECT STATEMENT  |          |
-- |*  1 |  TABLE ACCESS FULL| TQ84_TAB |
-- --------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter("NUM"="TQ84_PKG"."VAL"())

explain plan for
select txt from tq84_tab where num = (select tq84_pkg.val from dual);
select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- --------------------------------------
-- | Id  | Operation         | Name     |
-- --------------------------------------
-- |   0 | SELECT STATEMENT  |          |
-- |*  1 |  TABLE ACCESS FULL| TQ84_TAB |
-- |   2 |   FAST DUAL       |          |
-- --------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter("NUM"= (SELECT "TQ84_PKG"."VAL"() FROM "SYS"."DUAL" 
--               "DUAL"))

Cleaning up:

drop package tq84_pkg;
drop table   tq84_tab;

Oracle: Using a PL/SQL function in a WHERE clause

See also