Calling NVL and NVL2 function with a stored function. Function gets called even when value is not to be returned from that function. Please see following example.
create or replace
function f1
return varchar2
is
begin
---set serveroutput on
dbms_output.put_line('Call to F1');
return 1;
end;
set serveroutput on
select NVL2(null,f1,100) from dual
NVL2(NULL,F1,100)
--------------------------------------------------------------------------------
100
Call to F1
select nvl('x',f1) from dual
NVL('X',F1)
--------------------------------------------------------------------------------
x
Call to F1
So though the correct value is returned from the NVL and NVL2 function but function is also called.
This logic is used in many places and causing extra calls to the function causing performance degradation. Any help is appreciated.
Oracle version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production