Short-circuit Evaluations - moving away from NVL

In the experience I've had, I've found NVL such a ubiquitous expression. I started my Oracle career on 8.1.7, and it was the only expression available to filter through null expressions.

Then along came 9i. You have to look hard in the new features guide, but coalesce arrived as a "generalization of the NVL function."

To this day, I still encounter developers who have either never heard of it, or never utilise it. The more I think about the benefits of coalesce, the more I'm thinking perhaps we should stop using NVL altogether and get into the habit of using coalesce instead - regardless how how annoying it is to type.

First reason - it's more dynamic. We can determine the first non-null expression in a list. People use CASE expressions in lieu of coalesce all the time.
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Second reason - and most important - Oracle uses short-circuit evaluation, as per the ANSI standard.

Sometimes when we are comparing two values, one value could be more expensive to evaluate - perhaps it queries a table. In the scenario below I've simplified it to having a function that sleeps for the amount of seconds supplied.

10g>  create or replace function sw_delay (pn_seconds number) return number is
  2  begin
  3     dbms_lock.sleep(pn_seconds);
  4     return pn_seconds;
  5  end;
  6  /

Function created.

10g>  SET TIMING ON
10g>  -- this will always delay 1 second
10g>  select sw_delay(1) from dual;

SW_DELAY(1)
-----------
          1

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- While my function should never evaluate, the query still takes 1s
10g>  select nvl(2, sw_delay(1)) from dual;

NVL(2,SW_DELAY(1))
------------------
                 2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The same applies for nvl2
10g>  select nvl2(1, 2, sw_delay(1)) from dual;

NVL2(1,2,SW_DELAY(1))
---------------------
                    2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The ANSI standard does not evaluate unnecessary expressions.
10g>  select coalesce(2, sw_delay(1)) from dual;

COALESCE(2,SW_DELAY(1))
-----------------------
                      2

1 row selected.

Elapsed: 00:00:00.00
10g>  
10g>  -- Decode is acceptable
10g>  select decode(1,1,2,sw_delay(1)) from dual;

DECODE(1,1,2,SW_DELAY(1))
-------------------------
                        2

1 row selected.

Elapsed: 00:00:00.01
10g>  
10g>  -- As is Case 
10g>  select case when 1 = 1 then 2 else sw_delay(1) end from dual;

CASEWHEN1=1THEN2ELSESW_DELAY(1)END
----------------------------------
                                 2

1 row selected.

Elapsed: 00:00:00.00
10g>  select case 1 when 1 then 2 else sw_delay(1) end from dual;

CASE1WHEN1THEN2ELSESW_DELAY(1)END
---------------------------------
                                2

1 row selected.

Elapsed: 00:00:00.00

As commented in-line, there are occasions where there is no need to evaluate a second expression. Most programmers are taught early on to evaluate the cheapest expressions first, to save CPU time. The NVL and NVL2 expressions is not setup for this, however COALESCE, DECODE and both CASE expressions provide short circuit evaluations - once on expression returns true, there is no need to continue evaluating the superfluous entries, control returns.

The same goes for PL/SQL. Connor McDonald provides an interesting description of this here. Sometimes for the purposes of performance, it pays to be verbose.

Another version of this can be demonstrated below:

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if nvl(pn_seconds, sw_delay(1)) = 1 then
  4      return 'do this';
  5    else
  6      return 'do that';
  7    end if;
  8  end;
  9  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

Using NVL reads neatly, but regardless of the value passed, the expensive expression is always evaluated.

If it was to be rewritten with an OR operator, the expensive expression will be ignored.

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if pn_seconds is null
  4    or pn_seconds = sw_delay(1) then
  5      return 'do this';
  6    else
  7      return 'do that';
  8    end if;
  9  end;
 10  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
------------------------------
do this

1 row selected.

Elapsed: 00:00:00.01
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
------------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

What does the community think?