Perhaps to add a bit of detail - here it wasn't just a performance thing.
It literally broke the code, because we have logic in the function that's
called in the second argument of nvl(), and that logic is only valid if the
first argument that came in, was indeed NULL.
Sigh.
On Sun, Dec 8, 2019 at 3:18 PM Stefan Knecht <knecht.stefan@xxxxxxxxx>
wrote:
Ah, thanks Stefan - I should have checked there instead of on MoS :)
On Sun, Dec 8, 2019 at 3:12 PM Stefan Koehler <contact@xxxxxxxx> wrote:
Hello Stefan,
yes, this is a known behavior (called short-circuiting), works as
designed and may be impact the performance for specific data sets / queries.
Jonathan has written a blog post about this some time ago:
https://jonathanlewis.wordpress.com/2014/01/01/nvl-2/
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Stefan Knecht <knecht.stefan@xxxxxxxxx> hat am 8. Dezember 2019 um09:01 geschrieben:
broke a whole bunch of code:
Found this interesting. Very subtle difference in behavior, that just
right?
create or replace function foo return number as
begin
dbms_output.put_line('FOO CALLED');
return 42;
end;
/
set serverout on
select nvl(1, foo) from dual
/
select nvl2(1, foo, 2) from dual
/
select coalesce(1, foo) from dual
/
select case when 1 = 2 then foo else 1 end from dual
/
At first glance, these 4 statements should do the exact same thing,
the second argument, regardless of whether the first argument is NULL or
Yes and no:
SQL> select nvl(1, foo) from dual
2 /
NVL(1,FOO)
----------
1
FOO CALLED
SQL> select nvl2(1, foo, 2) from dual
2 /
NVL2(1,FOO,2)
-------------
42
FOO CALLED
SQL>
SQL> select coalesce(1, foo) from dual
2 /
COALESCE(1,FOO)
---------------
1
SQL> select case when 1 = 2 then foo else 1 end from dual
2 /
CASEWHEN1=2THENFOOELSE1END
--------------------------
1
So it appears that both NVL() and NVL2() are executing the function in
not.
consistently inconsistent - e.g. it happens both in a SQL and PL/SQL
Coalesce seems "smarter" and only executes the function if it needs to.
Does anyone see a reason why this is?
Tested this on 12.1 and 12.2, both show the same results. Behavior is
context.
Cheers
Stefan
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/
