As for repairing your code, if a bit of code is only valid for an inbound null
argument, that check for is not null turning the guts into a no-op is the
cohesive solution (also known in ancient times as a "firewall."). You can do
that with a wrapper and protect the fragile function within a package body or
use a wide variety of ways to project the fragility, or you can do it in the
function. Try to avoid doing it twice (or n) though: repetitive firewalls
instead of encapsulation can become significant and might underflow routine
detection in profilers despite being wasteful in aggregate. Avoid also
replicating the "if is not null" inline source code wrapper, which is an
example of writing redundant "sprinkled all over" application code that retains
the overhead even after the function is encapsulated.
THE PRECISE formulation of where to put "firewalls" has in the past been
something of a religious war. I am agnostic on that religious war other than
holding it should be done a single way in a given application suite.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Stefan Koehler
Sent: Sunday, December 08, 2019 3:26 AM
To: knecht.stefan@xxxxxxxxx
Cc: oracle-l-freelists
Subject: Re: Interesting difference between nvl() and coalesce()
Hello Stefan,
ah OK. Just to get the complete picture, please be also aware about the impact
on the CBO if you gonna change the code.
https://jonathanlewis.wordpress.com/2018/02/13/coalesce-v-nvl/
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 um 09:20--
geschrieben:
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.
