René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

December 28, 2005: On the lazy evaluation of decode and the eager evaluation of nvl

Here's a simple SQL select statement with a decode: 
select
  decode (2, 
    1, (select 'one'   from dual dual),
    2, (select 'two'   from dual),
    3, (select 'three' from dual)
  )
  from dual;
The statement returns: 
DECOD
-----
two
This is becuase the 2 on the same line as the decode matches the 2 on the line select 'two' from dual. Oracle does not have to evaluate the expressions (here: select statements) for the 1 and for the 3. This is called lazy evaluation because only expressions that are really needed are also evaluated.
In order to prove that Oracle employs lazy evaluation here, the following package is created: 
create package test_pck as

  procedure clr_;
  function  app_(p in varchar2) return varchar2;
  function  get_                return varchar2;

end test_pck;
/

create package body test_pck as

  txt varchar2(4000);

  procedure clr_ is begin
    txt := null;
  end clr_;

  function  app_(p in varchar2) return varchar2 is begin
     txt := txt || nvl(p, '<null>') || '-';
     return p;
  end app_;

  function get_ return varchar2 is begin
    return txt;
  end get_;

end test_pck;
/
The three functions/procedures are rather simple: clr_ clears (sets to null) the package private variable txt. app_ appends the parameter p to this package private variable, followed by a hyphen. If p is null, the literal <null> is appended. app_ then returns the value of p. Finally, get_ returns the package private variable txt.
With this package, it's possible to track which calls were made to app_.
First, I want to make the output of the following select statements a bit more readable, so I fix the width of all selected columns named out to 20 characters: 
column out_ format a20
I make sure that txt is set to null: 
exec test_pck.clr_;
I am ready to conduct my first experiment. The following decode statement compares 'b' to 'a', 'b' and 'c'. (Remember app_ just returns the value of the parameter passed). If 'b' matches 'a', a capital letter 'A' is returned, if it machtes 'b', a capital letter 'B' is returned, if it matches 'c', a capital letter 'C' is returned. If it matches none of these, an 'X' is returned. Obviously, in this simple case, a 'B' will be returned: 
select
  decode('b', test_pck.app_('a'), test_pck.app_('A'),
              test_pck.app_('b'), test_pck.app_('B'),
              test_pck.app_('c'), test_pck.app_('C'),
              test_pck.app_('X')
        ) out_
  from dual;

OUT_
--------------------
B
Let's see, which expressions were evaluated (or called) by the decode: 
select test_pck.get_ out_ from dual;

OUT_
--------------------
a-b-B-
As can be seen, Oracle made a call to app_ with 'a', found that it didn't match 'b', then made the second call to app_, this time with 'b', found that it matches 'b', and then called app_ with a 'B'. No other calls were made. No other calls were actually necessary, only those that were really needed were also executed.
Now, a similar experiment with nvl. 
exec test_pck.clr_;

select
  nvl(test_pck.app_(''), test_pck.app_('a')) out_
from dual;
The first expression to nvl was a null, so nvl returned the value of the second expression. 
OUT_
--------------------
a
Both calls to app_ were necessary, so both were executed: 
select test_pck.get_ out_ from dual;

OUT_
--------------------
<null>-a-
This time, the first expression in nvl is not null: 
exec test_pck.clr_;

select
  nvl(test_pck.app_('n'), test_pck.app_('a')) out_
from dual;
As the first expression to nvl was not null, the value of the first expression is returned: 
OUT_
--------------------
n
However, even though it was not necessary for the result of the above select statement, the second expression was still evaluated: 
select test_pck.get_ out_ from dual;
app_ was called twice, once with 'n' and once with 'a'. 
OUT_
--------------------
n-a-
This indicates clearly, that Oracle eagerly (as opposed to lazyly) evaluates an nvl.
For completness' sake, I do this experiment also with nvl2. 
exec test_pck.clr_;

select
    nvl2(null, test_pck.app_('1'), test_pck.app_('2')) out_
from dual;
The first expression here is not null, so the third is returned: 
OUT_
--------------------
2
Again, Oracle evaluates all expressions, even those that were not really used for the result: 
select test_pck.get_ out_ from dual;

OUT_
--------------------
1-2-

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.